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