Arrange columns of names & addresses into rows

I've got a list of names and addresses that I'm trying to organize for a mail merge. These list of names are in a CSV format, I've attached a file as an example. (I had to upload it as a xlsx file...Please change the file extension back to *.csv after you've downloaded it, as the as the forum only allow for excel & picture files). What I am trying to achieve is the final product which looks like the 2nd screenshot. As you can see from the screenshot, there is no consistency where the addresses are. Some are in one cell, some are on 2 cells... to make things worse, the list of names & addresses are in a 3 column format.

What is the best way to achieve this? Is VBA script the most appropriate solution or is there an Excel function that would ado the job?

Original File layout:
\1

Would like the result to look like this:
\1

SKMaverick,

it would be a lot easier to advise or provide a solution if you posted the workbook from which you took your screenshots. After all, you want help with Excel, not with pictures, right?

Edit: I see you attached a workbook. Unfortunately, my Excel tells me it's an invalid file format and I can't open it.

Try this on data after you've opened it Excel:

Option Explicit
Dim NR As Long, Col As Long

Sub SortAddress()
'Jerry Beaucaire   5/4/2010
Dim cFind As Range, cFirst As Range
Dim AddrCol As Long
On Error Resume Next

'Add missing spaces
    Set cFind = Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If cFind Is Nothing Then Exit Sub
    
    Set cFirst = cFind
    
    Do
        cFind.Offset(1, 0).Insert xlShiftDown
        Set cFind = Cells.FindNext(After:=cFind)
    Loop Until cFind.Address = cFirst.Address

'Setup table
    NR = 2
    Col = Cells(1, Columns.Count).End(xlToLeft).Column + 2
    With Range(Cells(1, Col), Cells(1, Col + 4))
        .Value = [{"First Name","Last Name","Address","City","Zip"}]
        .Font.Bold = True
    End With
    
'Split addresses with AREAS function
    For AddrCol = 1 To Col - 2
        ReformatData (AddrCol)
    Next AddrCol
End Sub


Sub ReformatData(AddrCol As Long)
'Jerry Beaucaire  5/4/2010
'Reorganize groups of data into row format
Dim i As Long, RNG As Range

Set RNG = Columns(AddrCol).SpecialCells(xlCellTypeConstants)

    For i = 1 To RNG.Areas.Count
        Cells(NR, Col) = Left(RNG.Areas(i)(1), InStrRev(RNG.Areas(i)(1), " ") - 1)
        Cells(NR, Col + 1) = Mid(RNG.Areas(i)(1), InStrRev(RNG.Areas(i)(1), " ") + 1)
        Select Case RNG.Areas(i).Cells.Count
            Case 3
                Cells(NR, Col + 2) = RNG.Areas(i)(2)
                Cells(NR, Col + 3) = Left(RNG.Areas(i)(3), InStr(RNG.Areas(i)(3), ",") - 1)
                Cells(NR, Col + 4) = Mid(RNG.Areas(i)(3), InStrRev(RNG.Areas(i)(3), " ") + 1)
            Case 4
                Cells(NR, Col + 2) = RNG.Areas(i)(2) & " " & RNG.Areas(i)(3)
                Cells(NR, Col + 3) = Left(RNG.Areas(i)(4), InStr(RNG.Areas(i)(4), ",") - 1)
                Cells(NR, Col + 4) = Mid(RNG.Areas(i)(4), InStrRev(RNG.Areas(i)(4), " ") + 1)
        End Select
        NR = NR + 1
    Next i

Set RNG = Nothing
End Sub

You can fix the one name that puts JR. alone as last name.

Just do a SAVE AS afterwards.

=========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.