Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glen
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

On 16 Apr 2006 03:25:25 -0700, "Glen" wrote:

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glen
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glen
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

Thanks for such a quick response. Very much appreciated.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Parse data where break is a first uppercase character in a string?

On 16 Apr 2006 09:07:43 -0700, "Glen" wrote:

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


Glad to help. Thanks for the feedback.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting a new line in spreadsheet Rental Man Excel Discussion (Misc queries) 2 January 9th 06 05:55 PM
Trying to match a text string to a data table, any suggestions? OCONUS Excel Worksheet Functions 3 December 2nd 05 07:39 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
I have ad address in one column. I need to break apart the data Justin Patrick New Users to Excel 1 June 2nd 05 06:39 AM


All times are GMT +1. The time now is 05:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"