Formula to Separate City from Address

[TaraAlexander]

I have a list that looks like:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Address[/TD]
[/TR]
[TR]
[TD]1820 E River Rd Ste 230Tucson[/TD]
[/TR]
[TR]
[TD]7550 E Mcdonald Dr Ste AScottsdale[/TD]
[/TR]
[TR]
[TD]442 Kaleimamahu StHonolulu[/TD]
[/TR]
[TR]
[TD]6 Larch Ave Ste 398Wilmington[/TD]
[/TR]
[TR]
[TD]85 Willow StNew Haven[/TD]
[/TR]
[TR]
[TD]64 Thompson St Ste A105East Haven[/TD]
[/TR]
[TR]
[TD]PO BOX 2590416 Slater RoadNew Britain[/TD]
[/TR]
[TR]
[TD]7059 Blair Rd Nw Ste 300Washington[/TD]
[/TR]
[TR]
[TD]1018 Bienville StNew Orleans[/TD]
[/TR]
[TR]
[TD]11131 N Oak Hills PkwyBaton Rouge[/TD]
[/TR]
[TR]
[TD]1304 Business Park DrTraverse City[/TD]
[/TR]
[TR]
[TD]1118 W Front StMonroe[/TD]
[/TR]
[TR]
[TD]121 Hunter Ave Ste 205Saint Louis[/TD]
[/TR]
</tbody>[/TABLE]

Need a formula to make it look like:

[TABLE="width: 800, align: left"]
<tbody>[TR]
[TD]Address[/TD]
[TD]Address 1[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]1820 E River Rd Ste 230Tucson[/TD]
[TD]1820 E River Rd Ste 230[/TD]
[TD]Tucson[/TD]
[/TR]
[TR]
[TD]7550 E Mcdonald Dr Ste AScottsdale[/TD]
[TD]7550 E Mcdonald Dr Ste A[/TD]
[TD]Scottsdale[/TD]
[/TR]
[TR]
[TD]442 Kaleimamahu StHonolulu[/TD]
[TD]442 Kaleimamahu St[/TD]
[TD]Honolulu[/TD]
[/TR]
[TR]
[TD]6 Larch Ave Ste 398Wilmington[/TD]
[TD]6 Larch Ave Ste 398[/TD]
[TD]Wilmington[/TD]
[/TR]
[TR]
[TD]85 Willow StNew Haven[/TD]
[TD]85 Willow St[/TD]
[TD]New Haven[/TD]
[/TR]
[TR]
[TD]64 Thompson St Ste A105East Haven[/TD]
[TD]64 Thompson St Ste A105[/TD]
[TD]East Haven[/TD]
[/TR]
[TR]
[TD]PO BOX 2590416 Slater RoadNew Britain[/TD]
[TD]PO BOX 2590416 Slater Road[/TD]
[TD]New Britain[/TD]
[/TR]
[TR]
[TD]7059 Blair Rd Nw Ste 300Washington[/TD]
[TD]7059 Blair Rd Nw Ste 300[/TD]
[TD]Washington[/TD]
[/TR]
[TR]
[TD]1018 Bienville StNew Orleans[/TD]
[TD]1018 Bienville St[/TD]
[TD]New Orleans[/TD]
[/TR]
[TR]
[TD]11131 N Oak Hills PkwyBaton Rouge[/TD]
[TD]11131 N Oak Hills Pkwy[/TD]
[TD]Baton Rouge[/TD]
[/TR]
[TR]
[TD]1304 Business Park DrTraverse City[/TD]
[TD]1304 Business Park Dr[/TD]
[TD]Traverse City[/TD]
[/TR]
[TR]
[TD]1118 W Front StMonroe[/TD]
[TD]1118 W Front St[/TD]
[TD]Monroe[/TD]
[/TR]
[TR]
[TD]121 Hunter Ave Ste 205Saint Louis[/TD]
[TD]121 Hunter Ave Ste 205[/TD]
[TD]Saint Louis[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

This can get a bit tricky, because of all the different possibilities. I don't know how to do it with native Excel functions, but can develop a User Defined Function in VBA that will handle most cases (works for all the examples that you posted). You just simply paste this code in a new Excel module in the VB Editor, and then use it like any other Excel function.

Here is the code:

Function GetCity(myAddress As String) As String


    Dim ln As Long
    Dim i As Long
    Dim curCap As Boolean
    Dim prvCap As Boolean
    Dim myAsc As Byte
    
'   Find length of string
    ln = Len(myAddress)
    
    curCap = False
    prvCap = False
'   If string is not empty, do the following
    If ln > 0 Then
'       Loop through string backwards
        For i = ln To 1 Step -1
'           Set previous value equal to current
            prvCap = curCap
'           Get current value
            myAsc = Asc(Mid(myAddress, i, 1))
            If myAsc >= 65 And myAsc <= 90 Then
                curCap = True
            Else
                curCap = False
            End If
'           Check to see if previous is caps and current is not a space
            If (prvCap = True) And (myAsc <> 32) Then
                GetCity = Mid(myAddress, i + 1, ln)
                Exit Function
            End If
        Next i
    End If
    
End Function

So, then if your first entry is in cell A1, then to get the city in cell C1, enter this formula in cell C1:
=GetCity(A1)

Then to get the rest of the address in cell B1, use this formula in that cell:
=SUBSTITUTE(A1,C1,"")

 

[Rick Rothstein]

Here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub GetCity()
  Dim R As Long, X As Long, Arr As Variant
  Arr = Range("A2", Cells(Rows.Count, "A").End(xlUp).Offset(, 1))
  For R = 1 To UBound(Arr)
    For X = Len(Arr(R, 1)) - 1 To 1 Step -1
      If Mid(Arr(R, 1), X, 2) Like "[! ][A-Z]" Then
        Arr(R, 2) = Mid(Arr(R, 1), X + 1)
        Arr(R, 1) = Left(Arr(R, 1), X)
        Exit For
      End If
    Next
  Next
  Range("B2:C2").Resize(UBound(Arr)) = Arr
End Sub[/td]
[/tr]
[/table]

 

[TaraAlexander]

Thank you Joe!

 

[TaraAlexander]

Thank you Rick!

 

[Joe4]

You are welcome.