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