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. |
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. |
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 |
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. |
Parse data where break is a first uppercase character in a string?
Thanks for such a quick response. Very much appreciated.
|
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 |
All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com