software

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 gt; Text to Columns, choosed the
'delimited' option and indicate that quot;,quot; is the delimiter. Let Excel do the
work for you!

quot;Kevin Gquot; wrote:

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

If the city is just one word, you can use

=MID(A1,FIND(quot; quot;,A1) 1,255)

If not use

=MID(A1,FIND(quot;~quot;,SUBSTITUTE(A1,quot; quot;,quot;~quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;
quot;,quot;quot;)))) 1,255)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Kevin Gquot; gt; wrote in message
news
gt; I have a column of Mexican city and state names in column A. The Mexican
gt; state names are of a variable length. What I would like to do is have a
gt; left() or right() function strip off the state name. The problem I have is
gt; that I can't do a generic number to pull of the state names because they
vary
gt; in length. Is there any way to do a dynamic length statement? One thing
I
gt; thought of but I'm not sure if it would work is a comma seperates the
state
gt; and city. I was trying to think of a way to use that comma as the key to
gt; strip off after that.
gt;
gt; Hopefully I was able to make a little bit of sense. Any help is
appreciated.
gt;
gt; Thanks,
gt;
gt; Kevin
I couldn't see the forest through the trees -- this worked perfectly!!!

Thanks,

Kevin

quot;bpeltzerquot; wrote:

gt; You could use the comma along with LEFT and FIND. But even easier is to
gt; highlight the column and select Data gt; Text to Columns, choosed the
gt; 'delimited' option and indicate that quot;,quot; is the delimiter. Let Excel do the
gt; work for you!
gt;
gt; quot;Kevin Gquot; wrote:
gt;
gt; gt; I have a column of Mexican city and state names in column A. The Mexican
gt; gt; state names are of a variable length. What I would like to do is have a
gt; gt; left() or right() function strip off the state name. The problem I have is
gt; gt; that I can't do a generic number to pull of the state names because they vary
gt; gt; in length. Is there any way to do a dynamic length statement? One thing I
gt; gt; thought of but I'm not sure if it would work is a comma seperates the state
gt; gt; and city. I was trying to think of a way to use that comma as the key to
gt; gt; strip off after that.
gt; gt;
gt; gt; Hopefully I was able to make a little bit of sense. Any help is appreciated.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Kevin