Text to Columns

[ericsh]

I need to break up a column of text that holds a combination of strings
and numbers. The problem is that the lines of text are not uniform. For
instance:

John Doe 123 23 Los Angeles California
James E Smith 23 1234 Austin Texas


How can I break up the text into columns as if it the text was comma
delimited like this:

John Doe, 123, 23, Los Angeles, California
James E Smith, 23, 1234, Austin, Texas


Thanks.

 

[Gary Keramidas]

i wrote this for another post and adapted it so it may be of use to you. you can
check the newsgroup for a topic help to a very special split for other ideas

i assumed all of you data was in column A

watch the word wrap from outlook express

Sub test2()
Dim i As Long, j As Long, n As Long, z As Long, y As Long

For y = 1 To 2
For i = 1 To Len(Range("a" & y).Value)
If Asc(Mid(Range("a" & y).Value, i)) >= 48 And Asc(Mid(Range("a" & _ '
split line here
y).Value, i)) <= 57 Then
Range("E" & y).Value = Left(Range("a" & y).Value, i - 1)
j = i
Exit For
End If
Next

For z = 1 To Len(Range("a" & y).Value)
If Asc(Right(Range("a" & y).Value, z)) >= 48 And Asc(Right(Range("A" & _
'split line here
y).Value, z)) <= 57 Then
Range("g" & y).Value = Right(Range("a" & y), z - 1)
n = z
Exit For
End If
Next

Range("f" & y).Value = Mid(Range("A" & y), j, Len(Range("a" & y)) - (j - 1 + n _
'split line here
- 1))
Next
End Sub

 

[ericsh]

Thanks Gary, this is just what I needed.