Extract address data

M

Marco Margaritelli

PLEASE, I need a formula to extract the data of an address string.
I know that I could use the =LEFT or =MID or =RIGHT formulas, but since the
lenght of the data varies, I will need something better than that.
The positive thing that the database I have is consistent as show below:
comma and space after the City, and space after the STate.


I have this in A1:
A1= Nashville, TN 37211

I would like a formula that extract the data from A1 giving me these results:
B1= Nashville
C1= TN
D1= 37211


THANKS!
 
E

Eduardo

Hi,
in B1

=TRIM(LEFT(A1,FIND(",",A1)-1))

in C1
=MID(A1,SEARCH(" ",A1)+1,3)

in D1

=TRIM(RIGHT(A1,FIND(" ",A1)-6))

hope this helps
 
R

Rick Rothstein

Try these...

B1: =LEFT(A1,LEN(A1)-10)
C1: =MID(A1,LEN(A1)-7,2)
D1: =RIGHT(A1,5)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top