Separating text - a little more than text to columns???

[JMattero]

Hello:
I need some help. I have a spreadsheet that has the following values
Cell A1 - Joe Smith
Cell A2 - 123 Main Street
Cell A3 - Anywhere, PA 19033
Cell A4 - Blank
Cell A5 - Jane Doe
Cell A6 - 456 State Street
Cell A7 - Somewhere, PA 19045

And this goes on to create a very long list of names and addresses. I would like to seperate the list so that column A is first name, column B is last name, column C is the street address (ie. 123 Main St), column D is the city, column E is the state, and column f is the zip code. I am familiar with how to seperate if all of column a contained first and last names, but I can't figure out how to seperate this when there are addresses in the rows. This is how the data was given to me, I didn't create it this way!!! Can anyone help? Thanks in advance.

Jeff

 

[jindon]

hi

this is quite similer to what i've just done

Sub test()
With ActiveSheet
    a = .Range("a1", .Cells(Rows.Count, "a").End(xlUp)).Value
    ReDim result(1 To UBound(a, 1) / 3, 1 To 6)
    For i = 1 To UBound(a, 1)
        If Not IsEmpty(a(i, 1)) Then
            ii = ii + 1: x = Left(a(i, 1), InStr(a(i, 1), Chr(32)))
            result(ii, 1) = Trim(x)
            Do While (a(i + n, 1)) <> ""
                Select Case n
                    Case 0
                        x = Trim(Replace(a(i, 1), x, ""))
                        result(ii, 2) = x
                    Case 1
                        result(ii, 3) = a(i + n, 1)
                    Case 2
                        x = Split(Replace(a(i + n, 1), ",", ""))
                        For iii = 0 To 2: result(ii, iii + 4) = x(iii): Next
                End Select
                n = n + 1
                If i + n > UBound(a, 1) Then Exit Do
            Loop
            i = i + n: n = 0
        End If
    Next
    .Range("c1").Resize(UBound(result, 1), 6) = result
End With

 

[JMattero]

Jindon:
I am not a code writer, but I am open to learning. I see the code you have written, but I need instructions on what to do with it. I know there is a way to write code for a sheet, but have never done it. Can you direct me on exactly what to do? Thanks.

 

[jindon]

1) hit Alt + F11 to open VB editor
2) go to Insert -> Module then paste the code onto right pane
3) close the window to get back to Excel
4) go to Tools -> Macro -> Macro then select "test" and Run