Extract address data

  • Thread starter Thread starter Marco Margaritelli
  • Start date Start date
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!
 
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
 
Back
Top