extracting 2nd string of word

[bluepenink]

Hello

Ive managed to be able to extract the second string or word in a certain cell

i.e. 5 Atlanta-Charleston

I used the following formualt to get: Atlanta-Charleston

=MID(SUBSTITUTE(E42," ","^",LEN(E42)-LEN(SUBSTITUTE(E42," ",""))),FIND("^",SUBSTITUTE(E42," ","^",LEN(E42)-LEN(SUBSTITUTE(E42," ",""))))+1,256)

where E42 = 5 Atlanta-Charleston

my PROBLEM i run into, is when i the city name changes to i.e.
7 Houston-Austin-San Antonio-Albur
or
4 Wichita-Oklahoma City

Basically, i want to replace the first character i.e. the # and just get the text string...can someone pls help!

cheers

 

[Jonmo1]

So you just want to remove the first word (or number)..
Basically, remove up to and including the first space?

Try

=REPLACE(E42,1,FIND(" ",E42),"")

Hope that helps.

 

[njimack]

Based on the sample you posted, you should be able to use:
=RIGHT(E42,LEN(E42)-2)

If however, the number at the start of the string could be more than 1 digit, use:
=RIGHT(E42,LEN(E42)-FIND(" ",E42))

 

[bluepenink]

wow thxs guys! awesome!!!!

 

[texasalynn]

also the asap add in has some great functions, one of them to remove number from text and vs versus

www.asap-utilities.com