Format Address, City, State, Zip-code

[MINDY9805]

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.

 

[johnnyL]

You showed what the starting data looks like.
What do you want the final result to look like?

 

[MINDY9805]

You showed what the starting data looks like.
What do you want the final result to look like?

I would want my end result to look like this.

 

[johnnyL]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Line 1	Line 2	Line 3	Line 4	Line 5		street address	city	state	zip
2	Charles Smith	35 Court St	Newtown PA 12341-0923				35 Court St	Newtown	PA	12341-0923
3	Gerald Will	Elizabeth Flowers	Joe Buz	3781 S Singer St	Los Angeles CA 13824-1845		3781 S Singer St	Los Angeles	CA	13824-1845
4	Mindy Cat	Will Musk	2948 E Lapham BLV	GreatNeck NY 18321-8371			2948 E Lapham BLV	GreatNeck	NY	18321-8371
5	Elon Musk	1249 4th St N	Apt 291	St Anthony FL 21242-3924			1249 4th St N Apt 291	St Anthony	FL	21242-3924
6	Adam Sandler	Mila Kunis	3474 Glenndale Blvd	Unit 482	Naples FL 38201		3474 Glenndale Blvd Unit 482	Naples	FL	38201
7	Tom	Jerry	1234 Looney Tune Way	Stage 1	Hollywood CA 99999		1234 Looney Tune Way Stage 1	Hollywood	CA	99999
8
Sheet1

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

 

[johnnyL]

Same results, just prettied up the code a bit to make it less ugly:

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