Sub SplitAddress()
Dim rng As Range
Dim str As String
Dim word() As String
Dim iw As Integer
Dim isep As Integer
For Each rng In Range( _
Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))
str = rng.text
word = Split(str, " ")
For iw = LBound(word) To UBound(word)
If Not IsNumeric(word(iw)) And _
UCase(word(iw)) = word(iw) Then Exit For
Next iw
If iw <= UBound(word) Then
isep = InStr(1, str, word(iw))
rng.Value = Trim(Left(str, isep - 1))
rng.Offset(0, 1).Value = Mid(str, isep, 256)
End If
Next rng
End Sub

HTH
--
AP

"Glen" a écrit dans le message de
ups.com...
I am trying to parse text string cells of various lengths and seperated
by spaces. I have thousands of rows of data. I can parse with break
lines at the spaces, however I need to parse where the first whole word
is in uppercase and for that word (in this example CURRAMBINE) to be in
the same column when parsed. Below are two rows of data of differing
lengths as an example:

(Row 1) 2 Paddington Avenue CURRAMBINE WA 6028
(Row 2) Currambine Station Carpark 1 Paddington Avenue CURRAMBINE WA
6028

Ideally I would like the data in columns as follows:

(Row 1 - Column 1) 2 Paddington Avenue (Row 1 - Column 2) CURRAMBINE
WA 6028
(Row 2 - Column 1) Currambine Station Carpark 1 Paddington Avenue (Row
2 - Column 2) CURRAMBINE WA 6028

I hope I have explained this correctly. Any assistance would be
appreciated.