software

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.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, quot;Aquot;), _
Cells(Rows.Count, quot;Aquot;).End(xlUp))
str = rng.text
word = Split(str, quot; quot;)
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 lt;= 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

quot;Glenquot; gt; a écrit dans le message de ups.com...
gt; I am trying to parse text string cells of various lengths and seperated
gt; by spaces. I have thousands of rows of data. I can parse with break
gt; lines at the spaces, however I need to parse where the first whole word
gt; is in uppercase and for that word (in this example CURRAMBINE) to be in
gt; the same column when parsed. Below are two rows of data of differing
gt; lengths as an example:
gt;
gt; (Row 1) 2 Paddington Avenue CURRAMBINE WA 6028
gt; (Row 2) Currambine Station Carpark 1 Paddington Avenue CURRAMBINE WA
gt; 6028
gt;
gt; Ideally I would like the data in columns as follows:
gt;
gt; (Row 1 - Column 1) 2 Paddington Avenue (Row 1 - Column 2) CURRAMBINE
gt; WA 6028
gt; (Row 2 - Column 1) Currambine Station Carpark 1 Paddington Avenue (Row
gt; 2 - Column 2) CURRAMBINE WA 6028
gt;
gt; I hope I have explained this correctly. Any assistance would be
gt; appreciated.
gt;
On 16 Apr 2006 03:25:25 -0700, quot;Glenquot; gt; wrote:

gt;I am trying to parse text string cells of various lengths and seperated
gt;by spaces. I have thousands of rows of data. I can parse with break
gt;lines at the spaces, however I need to parse where the first whole word
gt;is in uppercase and for that word (in this example CURRAMBINE) to be in
gt;the same column when parsed. Below are two rows of data of differing
gt;lengths as an example:
gt;
gt;(Row 1) 2 Paddington Avenue CURRAMBINE WA 6028
gt;(Row 2) Currambine Station Carpark 1 Paddington Avenue CURRAMBINE WA
gt;6028
gt;
gt;Ideally I would like the data in columns as follows:
gt;
gt;(Row 1 - Column 1) 2 Paddington Avenue (Row 1 - Column 2) CURRAMBINE
gt;WA 6028
gt;(Row 2 - Column 1) Currambine Station Carpark 1 Paddington Avenue (Row
gt;2 - Column 2) CURRAMBINE WA 6028
gt;
gt;I hope I have explained this correctly. Any assistance would be
gt;appreciated.

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

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

For first quot;splitquot;
=REGEX.MID(A1,quot;.*?(?=\b[A-Z] \b)quot;)

For second quot;splitquot;
=REGEX.MID(A1,quot;\b[A-Z] \b.*quot;)

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

Absolutely superb. Thank you for suc a quick response that works
perfectly!! Thank you.Thanks for such a quick response. Very much appreciated.On 16 Apr 2006 09:07:43 -0700, quot;Glenquot; gt; wrote:

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

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