Separating City from Address When Only Comma Sits Between City and State

nelobynature

New Member
Joined
Sep 20, 2013
Messages
4
Looking to extract the City from an address (which can be more than one or two words (ex: Winter Garden or Miami). The format I have is as follows: Address [space] City [comma] State Abbv [Space] Zip examples below:

6615 Mahan Dr Tallahassee, FL 32308
3838 S Dale Mabry Hwy Tampa, FL 33611
999 E Commercial Blvd Oakland Park, FL 33334
7550 NW 104th Ave Doral, FL 33178
201 NE Palm Coast Pkwy NE Palm Coast, FL 32137

All the solutions I've found on the boards either have all commas or spaces as deliminator which doesn't work for this one.

Thanks in advance,
Nelson
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I don't know how to tell where the city begins, so I tried something else in a macro.
You'll see that I didn't catch "Pkwy NE" but did catch a revision of that to "NE Pkwy".

I listed the addresses in column A, the type of suffix for an address in column B, and ran the macro to create column C.
I'll have to scratch my head on that one issue which I assume might come up frequently and see if there's a way around it unless someone comes up with another alternative.

[TABLE="width: 605"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]6615 Mahan Dr Tallahassee, FL 32308[/TD]
[TD]Dr[/TD]
[TD]Tallahassee[/TD]
[/TR]
[TR]
[TD]3838 S Dale Mabry Hwy Tampa, FL 33611[/TD]
[TD]Hwy[/TD]
[TD]Tampa[/TD]
[/TR]
[TR]
[TD]999 E Commercial Blvd Oakland Park, FL 33334[/TD]
[TD]Blvd[/TD]
[TD]Oakland Park[/TD]
[/TR]
[TR]
[TD]7550 NW 104th Ave Doral, FL 33178[/TD]
[TD]St[/TD]
[TD]Doral[/TD]
[/TR]
[TR]
[TD]201 NE Palm Coast Pkwy NE Palm Coast, FL 32137[/TD]
[TD]Ave[/TD]
[TD]NE Palm Coast[/TD]
[/TR]
[TR]
[TD]1 South 301 Pkwy West Palm Beach, FL 33414[/TD]
[TD]Pkwy[/TD]
[TD]West Palm Beach[/TD]
[/TR]
[TR]
[TD]201 NE Palm Coast NE Pkwy Palm Coast, FL 32137[/TD]
[TD]Way[/TD]
[TD]Palm Coast[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Circle[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Court
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Code:
Sub FLCity()
Dim LR As Long, i As Integer, j As Integer, addr As Long, a As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR
 For j = 1 To 6
   addr = InStr(1, Range("A" & i), Range("B" & j))
   If addr <> 0 Then
   a = addr + 1 + Len(Range("B" & j))
   addr = InStr(addr, Range("A" & i), ",") - 1
   Cells(i, 3) = Mid(Range("A" & i), a, addr - a + 1)
 Else
 End If
 Next j
Next i
End Sub

I thought another option might be to get a list of all cities in Florida and search that list, but haven't written anything to do that as yet.
 
Upvote 0
Code:
Sub FLCity2()
Dim LR As Long, LR2 As Long, i As Integer, addr As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
LR2 = Sheets("FLCities").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR
 For j = 1 To LR2
   addr = InStr(1, Range("A" & i), Sheets("FLCities").Range("A" & j))
   If addr <> 0 Then
   Cells(i, 3) = Sheets("FLCities").Range("A" & j)
 Else
 End If
 Next j
Next i
End Sub

After I found a list of FL cities (assume fairly accurate), I ran the macro above and that handles the Pkwy NE option.

Is this kinda cheating? If you're looking outside of FL, you'd have to find other lists and append them to this long list.

I found a list of FL cities here and put the list in column A of another sheet called FLCities: https://www.alphalists.com/list/alphabetical-list-florida-cities
 
Last edited:
Upvote 0
It can be done with a formula

Assuming address list is in column A

Enter formula in C1 and copy down
Code:
[B]=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A1,-LOOKUP(1,-SEARCH($B$1:$B$9&" ",A1)),50)," ",",",1),",",REPT(" ",50)),50,50)),"")[/B]
 
Upvote 0
This formula has the same problem with "Pkwy NE" as my original attempt.
 
Upvote 0
My formula will skip "Pkwy" and returns "NE Palm Coast" which which is consistent with NE (for "North East")
 
Upvote 0
Right, but the city is Palm Coast, not NE Palm Coast. The parkway is Pkwy NE
 
Upvote 0
Dear Allkey,

I am intrigued by your formula
=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A1,-LOOKUP(1,-SEARCH($B$1:$B$9&" ",A1)),50)," ",",",1),",",REPT(" ",50)),50,50)),"")

but don't understand how it works. Could you explain please?




I don't think you can be sure you've found the correct city unless you check against a reference.

You can't just check by possible city name (there's at least 10 cities in the USA called "Paris") and you can't just reference by zip as a city may have multiple zip codes (New York has over 200 zip codes) and a zip code may cross multiple cities (33178 is Doral and Medley). So we need to check both.

I found a site to download a city/zip list. It's free if you give them an attribution (so here's mine: Visit https://simplemaps.com/data/us-cities ) or you can pay money to avoid attribution. I have no relationship with the company but as it seems necessary to answer the question I thought the forum rules said it's OK to post it.

I actually wrote a solution but it uses so many worker columns and takes so long to run (I was temporarily stymied by the MATCH limit of 256 bytes for a wildcard search) that I'm embarrassed to post it. I'm sure one of the Gurus or VBA experts could find a 100% solution using that data.


 
Upvote 0
Right, but the city is Palm Coast, not NE Palm Coast. The parkway is Pkwy NE

The following code works assuming you are not on a MAC , your data is on the FIRST sheet in column A and you want the output in Column B and that you are only searching for US cities courtesy of the link provided by Toadstool. Please double check the output

Code:
Sub cities()
Dim WB_Path As String, WB2 As Workbook, WB2_DATA As Variant, States_Dictionary As Object, _
C_Dict As Object, X As Long, WB1_DATA As Variant, Keys As Variant, P_S() As String, Y As Long, RR As Range


Set States_Dictionary = CreateObject("Scripting.Dictionary")


Const URL As String = "https://simplemaps.com/static/data/us-cities/uscitiesv1.5.csv"


WB_Path = Environ("TEMP") & "\US_Cities.csv"
Call Get_File(URL, WB_Path)


Set WB2 = Workbooks.Open(WB_Path)


With WB2.Worksheets(1).UsedRange


      WB2_DATA = .Range(.Cells(2, 1), .Cells(.Rows.Count, 3)).Value2


End With


With States_Dictionary


    For X = 1 To UBound(WB2_DATA, 1) 'Create dictionary of cities within states
    
        If Not .Exists(WB2_DATA(X, 3)) Then
        
            Set C_Dict = CreateObject("Scripting.Dictionary")
        
            .Add WB2_DATA(X, 3), C_Dict
                    'state abbreviation used as key
        End If
        
        .Item(WB2_DATA(X, 3)).Add WB2_DATA(X, 1), ""
                    'city name used as key
    Next X


    Set RR = ThisWorkbook.Worksheets(1).UsedRange
    
    WB1_DATA = RR.Columns(1).Value2 'assumes address data is in first worksheet in column A
    
    ReDim Preserve WB1_DATA(1 To UBound(WB1_DATA, 1), 1 To 2)
    
    For X = 1 To UBound(WB1_DATA, 1)
    
        P_S = Split(WB1_DATA(X, 1), ",") 'string array containing address data
                            'stuff after comma          (Find state abbreviation and use as key for States_Dictionary)---> returns dictionary
        P_S(1) = Trim(P_S(1))
        
        On Error GoTo ABR_Not_Found
        TS = .Item(Split(P_S(1), " ")(0)).Keys 'cities were used as keys for specified dictionary
        
        For Y = LBound(TS) To UBound(TS)
                        'stuff before comma
            If InStrRev(P_S(0), TS(Y)) > 0 And InStrRev(P_S(0), TS(Y)) = Len(P_S(0)) - Len(TS(Y)) + 1 Then
            
                WB1_DATA(X, 2) = TS(Y)
                
                Exit For
            
            End If
        
        Next Y
skip:
    Next X


End With


RR.Columns(2).Value2 = WorksheetFunction.Index(WB1_DATA, 0, 2)


'Uncomment the line below if you want to close the US cities workbook
'WB2.CLOSE


Exit Sub
ABR_Not_Found:


    MsgBox "Abreviation " & Split(P_S(1), " ")(0) & " was not found within queried excel file. Please check " & _
    WB2.Name & " for a list of accepted state abbreviations."
     err.clear
    GoTo skip
End Sub
Public Function Get_File(File As String, SaveFilePathAndName As String)
Dim oStrm As Object, WinHttpReq As Object, Extension As String, File_Name As String
    
Set WinHttpReq = CreateObject("Msxml2.ServerXMLHTTP")
    WinHttpReq.Open "GET", File, False
    WinHttpReq.send
    File = WinHttpReq.responseBody
        If WinHttpReq.Status = 200 Then
            Set oStrm = CreateObject("ADODB.Stream")
            With oStrm
                .Open
                .Type = 1
                .Write WinHttpReq.responseBody
                .SaveToFile SaveFilePathAndName, 2 ' 1 = no overwrite, 2 = overwrite
                .Close
            End With
        End If
End Function
 
Last edited:
Upvote 0
The following code works assuming you are not on a MAC , your data is on the FIRST sheet in column A and you want the output in Column B and that you are only searching for US cities courtesy of the link provided by Toadstool. Please double check the output

The previous code works but changing the if statement to the following will reduce the likelihood of errors.
Code:
            If InStrRev(P_S(0), TS(Y)) = Abs(Len(P_S(0)) - Len(TS(Y))) + 1 Then
            
                WB1_DATA(X, 2) = TS(Y)
                
                Exit For
            
            End If
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top