Separating City Name From Addresses

[blackystrat]

Hi

Excel 2013

I have a list of 4422 addresses where the city name is suffixed at the end of the address.

The problem is that there is no delimiter in front of the city names and the number of words in the City Name varies between 1-3

How could I split them up so that the city is in a new column.

Please find a sample list attached which shows how I have it and how I want it - https://expirebox.com/download/87a60cc161ae45e497edb34c4410198a.html

Kindly advise

Thanks

 

[tonyyy]

blackystrat,

Every address in your sample list contains a number before the city name. Assuming this is true for all the addresses, you might consider the following...

Sub SplitCity_1026429()
Application.ScreenUpdating = False
Dim lenth As Long, i As Long
Dim r As Range
For Each r In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    lenth = Len(r.Value)
    For i = lenth To 1 Step -1
        If Mid(r.Value, i, 1) Like "[0-9]" Then
            r.Offset(0, 1).Value = Trim(Left(r.Value, i))
            r.Offset(0, 2).Value = Trim(Right(r.Value, lenth - i))
            Exit For
        End If
    Next i
Next r
ActiveSheet.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[tonyyy]

And since you have 4400 addresses, an array approach might be a bit quicker...

Sub SplitCity_1026429r2()
Application.ScreenUpdating = False
Dim lenth As Long, i As Long, n As Long
Dim arr As Variant
arr = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
For n = 1 To UBound(arr, 1)
    lenth = Len(arr(n, 1))
    For i = lenth To 1 Step -1
        If Mid(arr(n, 1), i, 1) Like "[0-9]" Then
            arr(n, 2) = Trim(Left(arr(n, 1), i))
            arr(n, 3) = Trim(Right(arr(n, 1), lenth - i))
            Exit For
        End If
    Next i
Next n
Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row) = arr
ActiveSheet.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

If you were looking for a formula approach..

Book1
	A	B	C
3	One E. Washington Street Ste. 1900 Phoenix	One E. Washington Street Ste. 1900	Phoenix
4	2525 E. Arizona Biltmore Circle Ste. B-218 Phoenix	2525 E. Arizona Biltmore Circle Ste. B-218	Phoenix
5	2575 E. Camelback Road Ste. 1100 Phoenix	2575 E. Camelback Road Ste. 1100	Phoenix
6	101 Montgomery Street Ste. 2300 San Francisco	101 Montgomery Street Ste. 2300	San Francisco
7	One Market Spear Tower Ste. 2200 San Francisco	One Market Spear Tower Ste. 2200	San Francisco
8	921 S. 8th Avenue Stop 8410 Pocatello	921 S. 8th Avenue Stop 8410	Pocatello
9	TCA - Cheri Mattson PO Box 126 Twin Falls	TCA - Cheri Mattson PO Box 126	Twin Falls
10	151 4th Street West Unit 3359 Ketchum	151 4th Street West Unit 3359	Ketchum
11	2300 N. Yellowstone Hwy. Ste. 210 Idaho Falls	2300 N. Yellowstone Hwy. Ste. 210	Idaho Falls
12	2235 E. 25th Street Ste. 250 Idaho Falls	2235 E. 25th Street Ste. 250	Idaho Falls
13	3456 E. 17th Street Ste. 280 Idaho Falls	3456 E. 17th Street Ste. 280	Idaho Falls
14	339 W. State Street Ste. 205 Eagle	339 W. State Street Ste. 205	Eagle
15	291 E. Shore Drive Ste. 200 Eagle	291 E. Shore Drive Ste. 200	Eagle
16	291 E. Shore Drive Ste. 200 Eagle	291 E. Shore Drive Ste. 200	Eagle
17	2825 S. Meridian Road Ste. 150 Meridian	2825 S. Meridian Road Ste. 150	Meridian
18	524 Third Street South PM Box 142 Nampa	524 Third Street South PM Box 142	Nampa
19	5700 E. Franklin Road Ste. 200 Nampa	5700 E. Franklin Road Ste. 200	Nampa
20	608 Northwest Blvd. Ste. 300 Coeur d'Alene	608 Northwest Blvd. Ste. 300	Coeur d'Alene
Sheet2

Cell Formulas
Range	Formula
B3	=LEFT(A3,LOOKUP(1,-MID(A3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A3))),1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A3)))))
C3	=TRIM(SUBSTITUTE(A3,B3,""))

 

[blackystrat]

Thank you so much, Tonyyy and Peter

Both of your solutions worked

 

[tonyyy]

Wow... I got mentioned in the same sentence as Peter!

You're very welcome, blackystrat. Glad it worked out...

 

[Peter_SSs]

You're very welcome, blackystrat. Glad it worked out...

Ditto