Splitting Address Info - Text to Columns
 

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?

Splitting Address Info - Text to Columns
 

On Fri, 12 Oct 2007 12:54:01 -0700, betany70
wrote:

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?

If the STATE is always a two letter abbreviation bounded by <spaces, 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

Splitting Address Info - Text to Columns
 

Beautiful - thanks!

"Ron Rosenfeld" wrote:

On Fri, 12 Oct 2007 12:54:01 -0700, betany70
wrote:

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?

If the STATE is always a two letter abbreviation bounded by <spaces, 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

Splitting Address Info - Text to Columns
 

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

"betany70" wrote in message
...
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?

Splitting Address Info - Text to Columns
 

On Fri, 12 Oct 2007 13:42:04 -0700, betany70
wrote:

Beautiful - thanks!

You're welcome. Thanks for the feedback.
--ron