Heh, heh... you made me go back and look at my UDF again... and I don't
think I need the WorksheetFunction.Trim call either.

Function RemDigitsPart(s As String) As String
Dim X As Long
Dim Parts() As String
Parts = Split(s, "(")
For X = 1 To UBound(Parts)
If X 1 Then RemDigitsPart = RemDigitsPart & ", "
RemDigitsPart = RemDigitsPart & Trim(Split(Parts(X), ")")(0))
Next
End Function

However, my UDF acts differently than yours for multiple spaces within the
text itself... I leave it there whereas you remove it. I can make the
argument for removing any extraneous outer spaces as the purpose of the UDF
seems to be to make a comma delimited list of the text located between the
parentheses and they would just "ugly up" the list; however, if there were
multiple internal spaces in the text, should we really be removing them (the
question being, why were they there in the first place... on purpose or by
mistake)? If the answer is yes, the multiple internal spaces should be
collapsed down to a single space, then in my code above, just swap out the
VBA Trim function for the Worksheet one. For the OP... that just means you
prepend WorksheetFunction. (notice the ending dot) onto the Trim function
call above.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Wed, 01 Jul 2009 21:54:24 -0400, Ron Rosenfeld

wrote:

On Wed, 1 Jul 2009 11:22:01 -0700, HammerD

wrote:

Sorry - my mistake... the cells have the text enclosed in parentheses.
And I
want just the text - not the number or parentheses. There may be 3 or
more
words within the parentheses.
(ex: i.e. - 260 (SMALL THINGS), 261 (THINGS)

I'm guessing that you want to retain the commas.

Here is a User Defined Function that will remove all digits, parentheses,
and
extraneous spaces from your string; leaving you with the text, and the
separating commas.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual
Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=RemDigPar(A1)

(where A1 contains your string to process) in some cell.

=====================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = Application.WorksheetFunction.Trim _
(re.Replace(s, ""))
End Function
==============================
--ron

Given Rick's (valid) concern about extraneous spaces that might not be
taken
care of by the worksheetfunction.trim, I have change the regex pattern to
take
care of that, and to also eliminate having to use worksheetfunction.trim
at
all:

================================
Option Explicit
Function RemDigPar(s As String) As String
Dim re As Object
Const sPat As String = "[\d()]+|(^\s+)|(\s+$)|(\s(?=\W))"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

RemDigPar = re.Replace(s, "")
End Function
===============================
--ron