Extracting text in a string.

  • Thread starter Thread starter Akond
  • Start date Start date
A

Akond

Using text function or otherwise, How do I extract the last text-block
from right till the 1st space from right in the string below:

"JOHN SMITH ANYCITY AZ 55555"

How to extact the zipcode?
I could use RIGHT(text,5) but that would not deal with cases that
might have the zip-zip4 as well.

Similarly, How to extact the State? This would always be the 2nd
text-block from the right.

Thanks.
 
One way:

Zip:

=MID(A1, FIND("$", SUBSTITUTE(A1," ","$", LEN(A1) -
LEN(SUBSTITUTE(A1, " ", ""))))+1, 10)


State:

=MID(A1, FIND("$", SUBSTITUTE(A1, " ", "$", LEN(A1) -
LEN(SUBSTITUTE(A1," ",""))-1))+1, 2)
 
Hi Akond!

This gives you the Zip Code:

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

This gives you the State Code:

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Akond!

If your Zip Codes can only be 4 or 5 numbers preceded by a space you
can use easier formulas:

For 4 or 5 digit Zip:
=TRIM(RIGHT(A1,5))

For State Code
=MID(A1,LEN(A1)-(LEN(TRIM(RIGHT(A1,5)))+2),2)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top