Separate address elements from 1 cell in Excel

Question

I have thousands of addresses in this format:

123 Happy St. Kansas City, MO 64521

9812 Main Street Minneapolis, MN 62154

12 Virgina Ave, Apt 8, Dallas, TX 54334

I want to extract the address, city, state, zip into individual cells (without using VB if possible). I've tried a couple variations of other methods posted, but I can't quite get desired results.

RELATED: https://stackoverflow.com/a/57645688/495455 – Jeremy Thompson Aug 25 '19 at 11:46 — Jeremy Thompson, Aug 25 '19 at 11:46

score 2 · Accepted Answer · edited May 23 '17 at 12:09

Analyze your problem!

you want to split your address string at the comma
you then want to split the right fragment from (1) at the first blank

ad 1): you get the position of the comma using =FIND(",", A1), and use the result in a =LEFT(...) and a =RIGHT(...) - for the latter you also need the string length (=LEN(...))

B1: =LEFT(A1;FIND(",";A1)-1)
C1: =RIGHT(A1;LEN(A1)-LEN(B1)-2)

Now comes the fun part ... in your 3rd example we mustn't split on the first comma, but on the third comma ... or as a more general rule, we always must split on the last comma .... but how do we find how many commas we have in the string, to feed its position as an additional argument into the =FIND(...) function?

Quick answer: look at Stackoverflow (exactly here) ... very clever ... subtract the length of the string with all commas removed from the original length, and then replace the last occurence of the comma by something else, because =SUBSTITUTE(...) works on occurence, whilst =FIND() only works on position. If you incorporate all this this, you will have

B1: =LEFT(A1;FIND("@";SUBSTITUTE(A1;",";"@"; LEN(A1)-LEN(SUBSTITUTE(A1;",";""))))-1) --> full address
C1: (same as above)

Here we use "@" as a neutral substitution string for the final comma as we asume that no address uses the "@"

ad 2): you apply the above (with blank instead of comma) once again to the right part. You can use the simple first version of the formulae as it's clear you want to split at the first blank

D1: =LEFT(C1;FIND(" ";C1)-1) --> state
E1: =RIGHT(C1;LEN(C1)-LEN(D1)-1) --> ZIP code

Is there a way to extract the city - and use logic to determine if the city has two words? (ie Kansas City, San Francisco, etc.) — user2320271, Jul 29 '13 at 13:34
From your sample data I'd say no, because you seem not to have a reliable delimiter between street address and city, it's either a blank or a comma — MikeD, Jul 29 '13 at 15:08

score 0 · Answer 2 · edited Jul 26 '18 at 21:03

This VBA function extracts Zip, State, City, Street1, and Street2 (Suite, Apt, etc.) into separate columns. Would need minor modification to remove commas.

Option Explicit

Function ParseAddress(ByVal varAddress As Variant, ByVal strAddressPart As String) As String

    Dim aryAddressTokens() As String
    Dim strCity As String
    Dim intCtr As Integer
    Dim intStreet2Tokens As Integer
    Dim strStreet1, strStreet2 As String

    If IsMissing(varAddress) Or varAddress = vbNullString Then
        ParseAddress = ""
    Else
        aryAddressTokens = Split(Trim(varAddress), " ")
        '
        If strAddressPart = "Zip" Then
            ParseAddress = aryAddressTokens(UBound(aryAddressTokens))
        ElseIf strAddressPart = "State" Then
            ParseAddress = UCase(aryAddressTokens(UBound(aryAddressTokens) - 1))
        ElseIf strAddressPart = "City" Then
            strCity = aryAddressTokens(UBound(aryAddressTokens) - 2)
            If Right(strCity, 1) = "," Then strCity = Left(strCity, Len(strCity) - 1)
            ParseAddress = strCity
        ElseIf strAddressPart = "Street1" Or strAddressPart = "Street2" Then
            'Find Street2 if present because Street1 output is dependent on it.
            ' Assume address never begins with a # or Suite.
            intCtr = 1
            strStreet2 = ""
            intStreet2Tokens = 0
            While (intCtr < UBound(aryAddressTokens) - 2) And strStreet2 = ""
                If Left(aryAddressTokens(intCtr), 1) = "#" Then
                    If Len(aryAddressTokens(intCtr)) = 1 Then
                        strStreet2 = aryAddressTokens(intCtr) & aryAddressTokens(intCtr + 1)
                        intStreet2Tokens = 2
                    Else
                        strStreet2 = aryAddressTokens(intCtr)
                        intStreet2Tokens = 1
                    End If
                ElseIf Left(aryAddressTokens(intCtr), 5) = "Suite" Then
                    If Len(aryAddressTokens(intCtr)) = 5 Then
                        strStreet2 = aryAddressTokens(intCtr) & " " & aryAddressTokens(intCtr + 1)
                        intStreet2Tokens = 2
                    Else
                        strStreet2 = aryAddressTokens(intCtr)
                        intStreet2Tokens = 1
                    End If
                ElseIf Left(aryAddressTokens(intCtr), 3) = "Apt" Then
                    strStreet2 = aryAddressTokens(intCtr) & " " & aryAddressTokens(intCtr + 1)
                    intStreet2Tokens = 2
                End If
                intCtr = intCtr + 1
            Wend
            If Not IsEmpty(strStreet2) Then
                If Right(strStreet2, 1) = "," Then strStreet2 = Left(strStreet2, Len(strStreet2) - 1)
            End If
            ' Now Street1.
            strStreet1 = ""
            For intCtr = 0 To UBound(aryAddressTokens) - (3 + intStreet2Tokens)
                strStreet1 = strStreet1 & " " & aryAddressTokens(intCtr)
            Next
            If Right(strStreet1, 1) = "," Then strStreet1 = Left(strStreet1, Len(strStreet1) - 1)
            'Assign.
            If strAddressPart = "Street1" Then
                ParseAddress = Trim(strStreet1)
            Else
                ParseAddress = Trim(strStreet2)
            End If
        End If
    End If
End Function

Separate address elements from 1 cell in Excel

2 Answers2

Linked