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

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!

"Kevin G" wrote:

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

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

Thanks,

Kevin

"bpeltzer" wrote:

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!

"Kevin G" wrote:

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

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)

"Kevin G" wrote in message
...
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