If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to extract capitalized group in text string
How would I extract the capitalized, 3 character group from a text string?
I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare -- newwbie |
#2
|
|||
|
|||
How to extract capitalized group in text string
On Tue, 26 Aug 2008 18:52:00 -0700, newbie
wrote: How would I extract the capitalized, 3 character group from a text string? I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare You can do it with a User Defined Function: alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To extract the first "stand-alone" three character code, use the following formula: =ReExtr(A1,"\b[A-Z]{3}\b") ============================== Option Explicit Function ReExtr(str As String, sPattern As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0).Value End If End Function =============================== --ron |
#3
|
|||
|
|||
How to extract capitalized group in text string
I inserted the code in VBA, inserted =ReExtr(A1,"\b[A-Z]{3}\b") at B1, and
got #NAME -- newwbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 18:52:00 -0700, newbie wrote: How would I extract the capitalized, 3 character group from a text string? I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare You can do it with a User Defined Function: alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To extract the first "stand-alone" three character code, use the following formula: =ReExtr(A1,"\b[A-Z]{3}\b") ============================== Option Explicit Function ReExtr(str As String, sPattern As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0).Value End If End Function =============================== --ron |
#4
|
|||
|
|||
How to extract capitalized group in text string
I didn't follow your instructions too well the first time. By going
INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) -- newwbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 18:52:00 -0700, newbie wrote: How would I extract the capitalized, 3 character group from a text string? I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare You can do it with a User Defined Function: alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To extract the first "stand-alone" three character code, use the following formula: =ReExtr(A1,"\b[A-Z]{3}\b") ============================== Option Explicit Function ReExtr(str As String, sPattern As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0).Value End If End Function =============================== --ron |
#5
|
|||
|
|||
How to extract capitalized group in text string
Here is UDF (implement it the same way you did Ron's UDF)...
Function ThreeUpperCaseLetters(R As Range) As String Dim X As Long Dim C As Range Dim Words() As String If R.Count = 1 Then For Each C In R Words = Split(" " & C.Value & " ") For X = UBound(Words) To 0 Step -1 If Words(X) Like "[A-Z][A-Z][A-Z]" Then ThreeUpperCaseLetters = Words(X) Exit Function End If Next Next Else ThreeUpperCaseLetters = Range("A0") End If End Function If there are more than one, the UDF always returns the last grouping of three-upper-case letters in the cell's text. It generates a #VALUE! error if you specify a range consisting of more than one cell (I wanted to force a #REF error, but I wasn't sure how to do that). Rick "newbie" wrote in message ... I didn't follow your instructions too well the first time. By going INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) -- newwbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 18:52:00 -0700, newbie wrote: How would I extract the capitalized, 3 character group from a text string? I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare You can do it with a User Defined Function: alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To extract the first "stand-alone" three character code, use the following formula: =ReExtr(A1,"\b[A-Z]{3}\b") ============================== Option Explicit Function ReExtr(str As String, sPattern As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0).Value End If End Function =============================== --ron |
#6
|
|||
|
|||
How to extract capitalized group in text string
I should mention, in case your examples were not representative, that the
group of three-upper-case letters that the UDF finds can be located anywhere in the text, but if it is not located at the beginning or end, then it must be separated from the other text by one or more spaces. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Here is UDF (implement it the same way you did Ron's UDF)... Function ThreeUpperCaseLetters(R As Range) As String Dim X As Long Dim C As Range Dim Words() As String If R.Count = 1 Then For Each C In R Words = Split(" " & C.Value & " ") For X = UBound(Words) To 0 Step -1 If Words(X) Like "[A-Z][A-Z][A-Z]" Then ThreeUpperCaseLetters = Words(X) Exit Function End If Next Next Else ThreeUpperCaseLetters = Range("A0") End If End Function If there are more than one, the UDF always returns the last grouping of three-upper-case letters in the cell's text. It generates a #VALUE! error if you specify a range consisting of more than one cell (I wanted to force a #REF error, but I wasn't sure how to do that). Rick "newbie" wrote in message ... I didn't follow your instructions too well the first time. By going INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) -- newwbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 18:52:00 -0700, newbie wrote: How would I extract the capitalized, 3 character group from a text string? I have 283 rows of text of varying lengths, each with a 3 character upper case string imbedded at any point, followed by more characters. I would like to isolate the 3 characters of upper case text and place them in a single column. Some examples a iPath S&P GSCI Crude Oil Tot Ret Idx OIL Specialty - Natural Resources UltraShort Financials ProShares SKF Specialty - Income holdings PowerShares DB Energy DBE Specialty - Healthcare You can do it with a User Defined Function: alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To extract the first "stand-alone" three character code, use the following formula: =ReExtr(A1,"\b[A-Z]{3}\b") ============================== Option Explicit Function ReExtr(str As String, sPattern As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = sPattern If re.test(str) = True Then Set mc = re.Execute(str) ReExtr = mc(0).Value End If End Function =============================== --ron |
#7
|
|||
|
|||
How to extract capitalized group in text string
On Tue, 26 Aug 2008 20:22:00 -0700, newbie
wrote: I didn't follow your instructions too well the first time. By going INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) Glad you figured out where to place the code. There are a variety of methods of changing this so it will pick up the LAST instance. One method is to change the pattern in the argument: "\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)" That translates into a command to return any three-character uppercase string that is not followed by another three-character upper case string So your formula would now look like: =ReExtr(A1,"\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)") --ron |
#8
|
|||
|
|||
How to extract capitalized group in text string
That works very well.
Thank you very much. -- newbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 20:22:00 -0700, newbie wrote: I didn't follow your instructions too well the first time. By going INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) Glad you figured out where to place the code. There are a variety of methods of changing this so it will pick up the LAST instance. One method is to change the pattern in the argument: "\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)" That translates into a command to return any three-character uppercase string that is not followed by another three-character upper case string So your formula would now look like: =ReExtr(A1,"\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)") --ron |
#9
|
|||
|
|||
How to extract capitalized group in text string
I assumed as much. I tried modifying the command with what I could gather
from other threads on similar objectives. I'm not VBA savvy, so I wasn't successful. Thank you very much. -- newwbie "Ron Rosenfeld" wrote: On Tue, 26 Aug 2008 20:22:00 -0700, newbie wrote: I didn't follow your instructions too well the first time. By going INSERT/MODULE, it works. Now I found a different instance where some of the text strings have more than one occurrence of 3 uppercase characters in varying locations. The code returns the first instance from the left, whereas I need the last instance in the string. (first from the right). Claymore CEF GS Connect ETN GCE Asset Allocation (returns CEF, need GCE) Glad you figured out where to place the code. There are a variety of methods of changing this so it will pick up the LAST instance. One method is to change the pattern in the argument: "\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)" That translates into a command to return any three-character uppercase string that is not followed by another three-character upper case string So your formula would now look like: =ReExtr(A1,"\b[A-Z]{3}\b(?!.*\b[A-Z]{3}\b)") --ron |
#10
|
|||
|
|||
How to extract capitalized group in text string
On Wed, 27 Aug 2008 05:17:01 -0700, newbie
wrote: That works very well. Thank you very much. -- newbie You're welcome. Thanks for the feedback. --ron |
Thread Tools | |
Display Modes | |
|
|