Splitting One field into many- How ?? 2

[SeeWard]

Hello there,
I have a table that was created by pulling in an Excel file. The issue is that there is that City, State, Zip, and County are....in one field. How can I break up the string so it is 4 fields respectively?
Thanks

 

[BoxHead]

SeeWard,

You can parse out the address string using the Mid(), Left() InsStr() and IsNumeric() functions.

EG: If you have a comma after every City Name, you can use

myCity = Left(myAddressField, inStr(MyAddressField,", ")-1)

The key is to find patterns on which to break out the string into its components. If this for US addresses only, the State should always be 2 characters, the zip should always be numeric...


HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[SeeWard]

Thanks John, You gave me the piece of the puzzle I was missing!
Cheers!

 

[EvilNewt25]

What if you have City State Zip + 4?

I have some City State Zip and some Zip+4 the format is:
ALLEN PARK MI 48101 (no commas)
and
ALLEN PARK MI 48101-1012 (no commas)
I only want the City (ALLEN PARK) portion how do I do this without counting from the left or right for many Cities in my dbf are different in length and also have space some don't? Thanks

 

[PHV]

Something like this ?
Left(AddressField, InStrRev(RTrim(AddressField)," ")-4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886