LEFT / RIGHT functions

K

Kevin G

I have a column of Mexican city and state names in column A. The Mexican
state names are of a variable length. What I would like to do is have a
left() or right() function strip off the state name. The problem I have is
that I can't do a generic number to pull of the state names because they vary
in length. Is there any way to do a dynamic length statement? One thing I
thought of but I'm not sure if it would work is a comma seperates the state
and city. I was trying to think of a way to use that comma as the key to
strip off after that.

Hopefully I was able to make a little bit of sense. Any help is appreciated.

Thanks,

Kevin
 
B

bpeltzer

You could use the comma along with LEFT and FIND. But even easier is to
highlight the column and select Data > Text to Columns, choosed the
'delimited' option and indicate that "," is the delimiter. Let Excel do the
work for you!
 
B

Bob Phillips

If the city is just one word, you can use

=MID(A1,FIND(" ",A1)+1,255)

If not use

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
K

Kevin G

I couldn't see the forest through the trees -- this worked perfectly!!!

Thanks,

Kevin
 
Top