On Mon, 2 Nov 2009 12:04:01 -0800, Dave L
wrote:

I'm writing a macro that will do a number of things, but I'm having trouble
with one part of it. I have a column that has text in it's values. What i
want to do is remove any text that is in parenthesis. So I a basically need
to remove anything after the "(" in a text string. If there are no
parenthesis in the cell then it should leave it alone. I'm sure this is
pretty easy, but I can't get the Find method to combine with Left to make is
work.

Your description and subject describe two different things.

To remove all text after some token (and to also remove the token and any
trailing spaces), you could use code such as:

===================
Function RemTextAfter(s As String, token As String) As String
RemTextAfter = Trim(Split(s, token)(0))
End Function
====================


However, if you want to remove all the text within parentheses, and I am
assuming you also want to remove the parentheses and leave only one space
between what is remaining, then:

=================================
Option Explicit
Function RemTextInParenth(s As String) As String
Dim re As Object
Const sPat As String = "\s*\([^)]+\)"

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

RemTextInParenth = re.Replace(s, "")
End Function
=================================

will handle multiple instances of text within parentheses. If there might be a
parentheses at the start of the string, change the next to last line to:

RemTextInParenth = Trim(re.Replace(s, ""))
--ron