MailingAddressesToColumns

[gh]

I have a spreadsheet with several hundred mailing addresses. They are
in rows like below with 1 0r 2 blank rows between each one.

Name
Address
City, State, Zip


How can I get them parsed to columns like below?

A B C D E
Name Address City State Zip


Thanks

[kowalskisan]

try this code. It assumes each address has only 3 rows of data as in your
example. This code assumes you addresses are in Col. A and copies the
addresses to columns B through F, starting in row 1:
Sub ParseAddresses()
Const COL As Integer = 1 ' assumes column with addresses is Col. A
Dim astrCityStateZip() As String 'Array to hold split City, State and Zip
Dim x As Long, lngLastRow As Long, lngAddressCount As Long
Dim blnName As Boolean, blnAddress As Boolean, blnCityStateZip As Boolean
lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row ' Get last row
lngAddressCount = 1 ' Count of addresses copied to columns B through F
blnName = True 'All boolean values are False by default
' Loop through all rows, from the top
For x = 1 To lngLastRow
If Cells(x, COL) <> "" Then
If blnName Then
Cells(lngAddressCount, COL + 1).Value = Cells(x, COL).Value
blnName = False
blnAddress = True
ElseIf blnAddress Then
Cells(lngAddressCount, COL + 2).Value = Cells(x, COL).Value
blnAddress = False
blnCityStateZip = True
ElseIf blnCityStateZip Then
astrCityStateZip = Split(CStr(Cells(x, COL).Value), ",")
Cells(lngAddressCount, COL + 3).Value = astrCityStateZip(0)
Cells(lngAddressCount, COL + 4).Value = astrCityStateZip(1)
Cells(lngAddressCount, COL + 5).Value = astrCityStateZip(2)
blnName = True
blnCityStateZip = False
lngAddressCount = lngAddressCount + 1 'increment the row for
results
End If
End If
Next x
End Sub


"gh" wrote:

> I have a spreadsheet with several hundred mailing addresses. They are
> in rows like below with 1 0r 2 blank rows between each one.
>
> Name
> Address
> City, State, Zip
>
>
> How can I get them parsed to columns like below?
>
> A B C D E
> Name Address City State Zip
>
>
> Thanks
>