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
|