Extracting A Portion Of A String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, Can anyone assist me with a formula to extract 2 characters out of a
string of text? For instance, the syntax of the string is:
Street Address(comma)City(comma)State(comma)Zip Code

What I need to extract are the 2 State characters.

Initially, I thought I could approach it from the righthand side of the
string, but then realized there was no consistency in the Zip Code format (ie
sometimes it wasn't included, sometimes it was 5 characters, sometimes it was
10 characters with hyphen, etc).

There is a consistent syntax that after the second comma, the next two
characters are for the state, but I'm having difficulty writing the formula
to identify those two characters.

Any and All Help is Appreciated - Thank You
 
Easiest method is probably Data > Text To Columns, indicate that that the
data is delimited by commas.
If you want a formula: =MID(E3,FIND(",",E3,FIND(",",E3)+1)+1,2) will return
the two characters immediately after the second comma. (If there's actually
a space before the state code, change the second +1 to a +2).
 
This formula assumes that the cell with the address is in A1 and the the
State will be "Comma Space and then 2 characters"...

=TRIM(MID(A1,FIND(",",A1,FIND(",",A1,1)+1)+1,3))

It looks for the second comma and grabs the next 3 characters. It trims
those characters to remove any blanks...
 
Thank You - I'll give it try!

bpeltzer said:
Easiest method is probably Data > Text To Columns, indicate that that the
data is delimited by commas.
If you want a formula: =MID(E3,FIND(",",E3,FIND(",",E3)+1)+1,2) will return
the two characters immediately after the second comma. (If there's actually
a space before the state code, change the second +1 to a +2).
Click to expand...
 
Back
Top