Extract address data
 

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!

Extract address data
 

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


"Marco Margaritelli" wrote:

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!

Extract address data
 

Try these...

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

--
Rick (MVP - Excel)


"Marco Margaritelli" wrote in
message ...
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!