Text to Column function in Access

[Guest]

Hello,

I have a Column called City State Zip that houses the info in this format.
City, ST, Zip. I am trying to create a query that will seperate the fields
into seperate Columns City, State, and Zip. I have everything working except
Zip. The problem with Zip is that sometimes the Zip is 00000-0000 format not
always 00000 format.

Here is an example field:
Dallas, TX 75006
Dallas, TX 75006-1234

Per a previous post by another user I was able to sperate the City using the
following code:

City1: Trim(Left([bidders]![City State Zip],InStr([bidders]![City State
Zip],",")-1))

The state was seperated by:

State: Left(Trim(Mid([bidders]![City State Zip],InStr([bidders]![City State
Zip],",")+1)),2)

I have tried to do this for the Zip, but I can not seem to get it to work.
I thought that if I could take the State code and put it in like this it
would work:

Zip:Trim(Left(Trim(Mid([bidders]![City State Zip],InStr([bidders]![City
State Zip],â€,â€)+1)),InStr((Trim(Mid([bidders]![City State
Zip],InStr([bidders]![City State Zip],â€,â€)+1)),†“)+1)))

Any thoughts on this would be greatly appreciated!

Chris

 

[Allen Browne]

If the records all have a comma, space, and 2-character state, you could
use:

Trim(Mid([City State Zip],Instr([City State Zip], ", ")+4))