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

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

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

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

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

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

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

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

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

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