Split up data (1 Viewer)

[kitty77]

I have a table that has the city, state and zip combined in one field.

Looks like this: Columbus, OH 43217

How can I split up the data into three separate fields in a query?

Thanks!

 

[MadPiet]

I always do this in PowerQuery now... but I'd do it like this:
1. split the string on the comma. The "left" side is City. The right side is State and Zip Code
2. Trim the State and ZipCode to remove the leading and trailing spaces.
3. Split the result on [Space] or double-space. then you get State (left) and Zip Code (right).

 

[Cotswold]

another way is using string handling

mVar1 = Len(Trim(Field))
mVar2 = instr(field,",")
mVar3 = instr((mVar2+2),Field,Space(1))

mNew1 = left(field,(mVar2-1))
mNew2 = mid(field, mVar2+2, 2)
mNew3 = Right(trim(field),mVar1-mVar3+1)

 

[xavier.batlle]

In a perfect world, if the data always follows the same pattern, this could be a way to achieve it.

SELECT Customers.Address, Left([Address],InStr([Address],",")-1) AS City, Mid([Address],InStr([Address],",")+2,2) AS State, Mid([Address],InStrRev([Address]," ")+1) AS Zip
FROM Customers;