Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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. 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), "(") 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 -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
On Mon, 2 Nov 2009 16:04:52 -0500, "Rick Rothstein"
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. 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), "(") 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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. 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), "(") 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 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. 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). -- Rick (MVP - Excel) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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) You can handle that by concatenating an opening parentheses onto the text... Sheet1.Cells(i, 2) = Split(mystring & "(", "(")(0) -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove text after character
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a text character from an excel cell? | Excel Worksheet Functions | |||
Remove last character of text string | Excel Worksheet Functions | |||
How do I remove all text in a cell after a specific character? | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |