remove text after character

  • Thread starter Thread starter Dave L
  • Start date Start date
D

Dave L

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.
 
If there is no data after what is in parenthesis then
mystring = Sheet1.Cells(i, 1)
Sheet1.Cells(i, 2) = Split(mystring, "(")(0)
 
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.
Click to expand...

Try this function:

Function remove_after_character(s As String, c As String) As String
If InStr(s, c) Then
remove_after_character = Left(s, InStr(s, c))
Else
remove_after_character = s
End If
End Function

use e.g. like this:

Cells(1,1) = remove_after_character(Cells(1,1), "(")

Hope this helps / Lars-Åke
 
I'm writing a macro that will do a number of things, but I'm having
Try this function:

Function remove_after_character(s As String, c As String) As String
If InStr(s, c) Then
remove_after_character = Left(s, InStr(s, c))
Else
remove_after_character = s
End If
End Function

use e.g. like this:

Cells(1,1) = remove_after_character(Cells(1,1), "(")
Click to expand...

You accidentally left out the -1 (minus one) off of the second argument in
the Left function call. Without it, the character passed into the 'c'
argument will be displayed.

As a point of interest, you can simplify this function down to a one-liner
by doing it this way...

Function Remove_After_Character(s As String, c As String) As String
Remove_After_Character = Left(s, InStr(s & c, c) - 1)
End Function
 
You accidentally left out the -1 (minus one) off of the second argument in
the Left function call. Without it, the character passed into the 'c'
argument will be displayed.

As a point of interest, you can simplify this function down to a one-liner
by doing it this way...

Function Remove_After_Character(s As String, c As String) As String
Remove_After_Character = Left(s, InStr(s & c, c) - 1)
End Function
Click to expand...

Well, I had the -1 to start with, but then I chose to interpret the
OP request 'remove anything after the "(" in a text string', as after
and not including.
But it is always difficult with ambigous requests.
'remove any text that is in parenthesis'. What if there is text after
the closing parenthesis ")", like xxxxxx(xxxxx)xxxx
What if there is more than one parenthesis, like xxx(xx)xx(xx)xx
and so on?

But the one-liner is very nice. With the -1, I would name the function
Remove_From_Character.

Lars-Åke
 
I'm writing a macro that will do a number of things, but I'm having
Well, I had the -1 to start with, but then I chose to interpret the
OP request 'remove anything after the "(" in a text string', as after
and not including.
But it is always difficult with ambigous requests.
'remove any text that is in parenthesis'. What if there is text after
the closing parenthesis ")", like xxxxxx(xxxxx)xxxx
What if there is more than one parenthesis, like xxx(xx)xx(xx)xx
and so on?

But the one-liner is very nice. With the -1, I would name the function
Remove_From_Character.
Click to expand...

Ah, now I see why you didn't include the -1 (I knew you knew about it, I
just thought you typed it out directly and simply forgot it). Yeah, the OP's
request does say "after", but I just assumed he wouldn't really want the
outputted text to end with an opening parenthesis (hence my posting to you).
 
don't need the first line, the Split will work even if there are no
parentheses.

Sheet1.Cells(i, 2) = Split(mystring, "(")(0)

(but will fail if the cell, i.e. mystring, is empty)

formula is:

=IF(ISERROR(LEFT(A1,FIND("(",A1)-1)),A1,LEFT(A1,FIND("(",A1)-1))
 
don't need the first line, the Split will work even if there are no
parentheses.

Sheet1.Cells(i, 2) = Split(mystring, "(")(0)

(but will fail if the cell, i.e. mystring, is empty)
Click to expand...

You can handle that by concatenating an opening parentheses onto the text...

Sheet1.Cells(i, 2) = Split(mystring & "(", "(")(0)
 
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.
Click to expand...

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
 
Back
Top