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).

"MWS" wrote:

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

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...

--
HTH...

Jim Thomlinson


"MWS" wrote:

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

=MID(A3,FIND(",",A3,FIND(",",A3)+1)+1,2)

--
Regards,
Tom Ogilvy


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

Thank You - I'll give it try!

"bpeltzer" wrote:

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).

"MWS" wrote:

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