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