Parse data where break is a first uppercase character in a string?

[Glen]

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.

 

[Ardus Petus]

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

 

[Ron Rosenfeld]

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.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use these REgular Expression formulas to parse the data (in A1).

For first "split"
=REGEX.MID(A1,".*?(?=\b[A-Z]+\b)")

For second "split"
=REGEX.MID(A1,"\b[A-Z]+\b.*")

If you want to construct a VBA macro, either these formulas or the Microsoft
VBScript Regular Expressions could be used.


--ron

 

[Glen]

Absolutely superb. Thank you for suc a quick response that works
perfectly!! Thank you.

 

[Glen]

Thanks for such a quick response. Very much appreciated.

 

[Ron Rosenfeld]

Absolutely superb. Thank you for suc a quick response that works
perfectly!! Thank you.

Glad to help. Thanks for the feedback.
--ron