Format Address, City, State, Zip-code

MINDY9805

New Member
Joined
Mar 14, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Im looking to clean up some data. I need to extract a mailing address from the following data. The spreadsheet includes the street address in different columns and city,State and zip in one cell. Please help, I only need the street adress, city, state, zipcode.


1647285682490.png
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You showed what the starting data looks like.
What do you want the final result to look like?
 
Upvote 0
Book1
ABCDEFGHIJK
1Line 1Line 2Line 3Line 4Line 5street addresscitystatezip
2Charles Smith35 Court StNewtown PA 12341-092335 Court StNewtownPA12341-0923
3Gerald WillElizabeth FlowersJoe Buz 3781 S Singer StLos Angeles CA 13824-18453781 S Singer StLos AngelesCA13824-1845
4Mindy CatWill Musk 2948 E Lapham BLVGreatNeck NY 18321-83712948 E Lapham BLVGreatNeckNY18321-8371
5Elon Musk 1249 4th St N Apt 291 St Anthony FL 21242-39241249 4th St N Apt 291St AnthonyFL21242-3924
6Adam SandlerMila Kunis 3474 Glenndale BlvdUnit 482Naples FL 382013474 Glenndale Blvd Unit 482NaplesFL38201
7TomJerry 1234 Looney Tune WayStage 1Hollywood CA 999991234 Looney Tune Way Stage 1HollywoodCA99999
8
Sheet1


VBA Code:
Sub TestMindy9805()
'
    Dim CityLength          As Long
    Dim DataOutputArrayRow  As Long, SourceArrayRow     As Long
    Dim SourceArrayColumn   As Long
    Dim LastRowColumnA      As Long, LastRowColumnJ     As Long
    Dim City                As String, State            As String, ZipCode  As String
    Dim DataOutputArray     As Variant, SourceDataArray As Variant
'
    LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                                                          ' Get Last used row of Column A
    LastRowColumnJ = Range("J" & Rows.Count).End(xlUp).Row                                                          ' Get Last used row of Column J
    SourceDataArray = Range("A2:E" & LastRowColumnA)                                                                ' Save range of data into SourceDataArray
    DataOutputArrayRow = 0                                                                                          ' Initialize DataOutputArrayRow
'
    ReDim DataOutputArray(1 To UBound(SourceDataArray), 1 To 4)                                                     ' Set the # of rows and columns for DataOutputArray
'
    For SourceArrayRow = 1 To UBound(SourceDataArray, 1)                                                            ' Loop through the rows of SourceDataArray
        For SourceArrayColumn = 1 To UBound(SourceDataArray, 2)                                                     '   Loop through the columns of SourceDataArray
            If IsNumeric(Left$(Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn)), 1)) Then                   '       If NumberAddress Found then ...
                DataOutputArrayRow = DataOutputArrayRow + 1                                                         '           Increment DataOutputArrayRow
                DataOutputArray(DataOutputArrayRow, 1) = WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                        SourceArrayColumn))                                                                         '           Save street address to DataOutputArray
'
                If Len(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn + 1))) - _
                        Len(WorksheetFunction.Substitute(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                        SourceArrayColumn + 1)), " ", "")) = 1 Then                                                 ' If additional data for street addr found then ...
                    DataOutputArray(DataOutputArrayRow, 1) = DataOutputArray(DataOutputArrayRow, 1) & " " & _
                            WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn + 1))          '       Append it to the street address

                    ZipCode = Mid$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 2)), InStrRev(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 2)), " ") + 1)                                                          '       Find zip code in next column
                    State = Mid$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn + 2)), _
                            InStrRev(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn + 2)), _
                            " ") - 2, 2)                                                                                '       Find state in next column
                    CityLength = Len(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 2))) - Len(ZipCode) - Len(State) - 2                                    '       Calculate length of city in next col
                    City = Left$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 2)), CityLength)                                                        '       Get city
                Else                                                                                                    ' Else
                    ZipCode = Mid$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, SourceArrayColumn + 1)), _
                            InStrRev(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 1)), " ") + 1)                                                          '       Find zip code in next column
                    State = Mid$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 1)), InStrRev(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 1)), " ") - 2, 2)                                                       '       Find state in next column
                    CityLength = Len(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 1))) - Len(ZipCode) - Len(State) - 2                                    '       Calculate length of city in next col
                    City = Left$(WorksheetFunction.Trim(SourceDataArray(SourceArrayRow, _
                            SourceArrayColumn + 1)), CityLength)                                                        '       Get city
                End If
'
                DataOutputArray(DataOutputArrayRow, 2) = City                                                           ' Save City to DataOutputArray
                DataOutputArray(DataOutputArrayRow, 3) = State                                                          ' Save State to DataOutputArray
                DataOutputArray(DataOutputArrayRow, 4) = ZipCode                                                        ' Save ZipCode to DataOutputArray
                Exit For
            End If
        Next
    Next
'
    Range("G2:J" & LastRowColumnJ).Delete                                                                               ' Erase any previous results
    Range("G2:J" & LastRowColumnA) = DataOutputArray                                                                    ' Display results
End Sub
 
Upvote 0
Same results, just prettied up the code a bit to make it less ugly:

VBA Code:
Sub TestMindy9805V2()
'
    Dim DataOutputArrayRow      As Long, SourceArrayRow             As Long
    Dim SourceArrayColumn       As Long
    Dim LastRowColumnA          As Long, LastRowColumnJ             As Long
    Dim ArrayValue              As Variant, ArrayNextColumnValue    As Variant, Array2ColumnsAwayValue  As Variant
    Dim City                    As String, State                    As String, ZipCode  As String
    Dim DataOutputArray         As Variant, SourceDataArray         As Variant
'
        LastRowColumnA = Range("A" & Rows.Count).End(xlUp).Row                                                  ' Get Last used row of Column A
        LastRowColumnJ = Range("J" & Rows.Count).End(xlUp).Row                                                  ' Get Last used row of Column J
       SourceDataArray = Range("A2:E" & LastRowColumnA)                                                         ' Save range of data into SourceDataArray
    DataOutputArrayRow = 0                                                                                      ' Initialize DataOutputArrayRow
'
    ReDim DataOutputArray(1 To UBound(SourceDataArray), 1 To 4)                                                 ' Set the # of rows and columns for DataOutputArray
'
    For SourceArrayRow = 1 To UBound(SourceDataArray, 1)                                                        ' Loop through the rows of SourceDataArray
        For SourceArrayColumn = 1 To UBound(SourceDataArray, 2)                                                 '   Loop through the columns of SourceDataArray
            ArrayValue = SourceDataArray(SourceArrayRow, SourceArrayColumn)                                     '       Save column value so shorter code possible
            ArrayNextColumnValue = SourceDataArray(SourceArrayRow, SourceArrayColumn + 1)                       '       Save column value so shorter code possible
'
            If IsNumeric(Left$(WorksheetFunction.Trim(ArrayValue), 1)) Then                                     '       If NumberAddress Found then ...
                DataOutputArrayRow = DataOutputArrayRow + 1                                                     '           Increment DataOutputArrayRow
                DataOutputArray(DataOutputArrayRow, 1) = WorksheetFunction.Trim(ArrayValue)                     '           Save street address to DataOutputArray
'
                If Len(WorksheetFunction.Trim(ArrayNextColumnValue)) - _
                        Len(WorksheetFunction.Substitute(WorksheetFunction.Trim(ArrayNextColumnValue), _
                        " ", "")) = 1 Then                                                                      ' If additional data for street addr found then ...
                    Array2ColumnsAwayValue = SourceDataArray(SourceArrayRow, SourceArrayColumn + 2)             '       Save column value so shorter code possible
'
                    DataOutputArray(DataOutputArrayRow, 1) = DataOutputArray(DataOutputArrayRow, 1) & " " & _
                            WorksheetFunction.Trim(ArrayNextColumnValue)                                        '       Append it to the street address
'
                    ZipCode = Mid$(WorksheetFunction.Trim(Array2ColumnsAwayValue), _
                          InStrRev(WorksheetFunction.Trim(Array2ColumnsAwayValue), " ") + 1)                    '       Find zip code in next column
                      State = Mid$(WorksheetFunction.Trim(Array2ColumnsAwayValue), _
                          InStrRev(WorksheetFunction.Trim(Array2ColumnsAwayValue), " ") - 2, 2)                 '       Find state in next column
                      City = Left$(WorksheetFunction.Trim(Array2ColumnsAwayValue), _
                             InStr(WorksheetFunction.Trim(Array2ColumnsAwayValue), State) - 2)                  '       Find city in next column
                Else                                                                                            ' Else
                    ZipCode = Mid$(WorksheetFunction.Trim(ArrayNextColumnValue), _
                          InStrRev(WorksheetFunction.Trim(ArrayNextColumnValue), " ") + 1)                      '       Find zip code in next column
                      State = Mid$(WorksheetFunction.Trim(ArrayNextColumnValue), _
                          InStrRev(WorksheetFunction.Trim(ArrayNextColumnValue), " ") - 2, 2)                   '       Find state in next column
                      City = Left$(WorksheetFunction.Trim(ArrayNextColumnValue), _
                             InStr(WorksheetFunction.Trim(ArrayNextColumnValue), State) - 2)                    '       Find city in next column
                End If
'
                DataOutputArray(DataOutputArrayRow, 2) = City                                                   ' Save City to DataOutputArray
                DataOutputArray(DataOutputArrayRow, 3) = State                                                  ' Save State to DataOutputArray
                DataOutputArray(DataOutputArrayRow, 4) = ZipCode                                                ' Save ZipCode to DataOutputArray
                Exit For
            End If
        Next
    Next
'
    Range("G2:J" & LastRowColumnJ).Delete                                                                       ' Erase any previous results
    Range("G2:J" & LastRowColumnA) = DataOutputArray                                                            ' Display results
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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