Split City And State

[jo15765]

I have a table that has city, state zip in one field so the data reads like this

City, State Zip

or a more literal example is

Atlanta, GA 00000

How can I split the State (GA) out of that field and into it's own column in the query?

[Welshgasman]

Use the Split() function

https://docs.microsoft.com/en-us/off...split-function

[Welshgasman]

oops, seems no available in a query
You could use Instr() twice?, once to find first space (or the comma) and once to find the next space. along with a mid() ?

Alternative create your own Split?

Public Function MySplit(strToSplit As String, iIndex As Integer)

MySplit = Split(strToSplit)(iIndex)

End Function

? mysplit("Atlanta, GA 00000",1)
GA

HTH

[June7]

City: Left(fieldname, InStr(fieldname, ",") - 1)

State: Mid(fieldname, InStr(fieldname, ",") + 2, 2)

Zip: Mid(fieldname, InStrRev(fieldname, " ") + 1)

[Micron]

Alternative create your own Split?

...and call it from a query (if need be).
Or Instr +2 to find 1st character of state, then Mid(of that point,2)?