Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Parse data where break is a first uppercase character in a string?
Thanks for such a quick response. Very much appreciated.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Trying to match a text string to a data table, any suggestions? | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
I have ad address in one column. I need to break apart the data | New Users to Excel |