Split City And State

**jo15765** · 06-08-2021, 11:09 AM

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

Code:

City, State Zip

or a more literal example is

Code:

Atlanta, GA 00000

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

**Welshgasman** · 06-08-2021, 11:32 AM

Use the Split() function

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

**Welshgasman** · 06-08-2021, 11:39 AM

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?

Code:

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

MySplit = Split(strToSplit)(iIndex)

End Function

Code:

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

HTH

**June7** · 06-08-2021, 11:53 AM

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

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

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

**Micron** · 06-08-2021, 11:58 AM

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)?

Split City And State

Thread Tools

Split City And State

Similar Threads

API to find City,State given a zip code

Create address, city, state, zip columns

Prefill the City and State upon entering the Zip on a form

Texas City,State,Zip and Area Code

City, State Zip lookup

Posting Permissions