Splitting Address Info - Text to Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello-

Our database sends our adress info without specific delimters - ie
City_State_Zip (I know this should mean that it is delimted by a space but we
have several cities with a space in the name :) Is there a way to split
based on the text contained - ie everything before the first two cap
COmbination or Enter a specific state abrieviation etc?
 
Hello-

Our database sends our adress info without specific delimters - ie
City_State_Zip (I know this should mean that it is delimted by a space but we
have several cities with a space in the name :) Is there a way to split
based on the text contained - ie everything before the first two cap
COmbination or Enter a specific state abrieviation etc?
Click to expand...

If the STATE is always a two letter abbreviation bounded by <space>s, and the
Zip is at the end with no included spaces, then you could use the following
formulas:

City: All words up to next-to-last <space>

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

State: The two characters following next-to-last <space>

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

Zip: All the characters after the last <space>

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


--ron
 
Beautiful - thanks!

Ron Rosenfeld said:
If the STATE is always a two letter abbreviation bounded by <space>s, and the
Zip is at the end with no included spaces, then you could use the following
formulas:

City: All words up to next-to-last <space>

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

State: The two characters following next-to-last <space>

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

Zip: All the characters after the last <space>

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


--ron
Click to expand...
 
Have your "database" send you the information, in a format acceptable to
Excel (or any other software). i.e. City, delimiter, State delimiter, etc
Almost all software is capable of creating delimited files.

Tyro
 
Back
Top