Remove Characters (Office 2003)

Topic

New Reply

Hi All,

I have a file which I want to remove the ” ( R ) ” from the text in the column. However, the file which I download may show some spacing before the ” ®” such as :
AAA ®
BB ( R )
CCCC ( R )
DD ®

Is it possible to remove the bracket and the R leaving just the text before it like “AAA” via a macro?

Thanks for your assistance in this.

regards, francis

Reply | Quote

Replies

If your data are in A1, A2 etc., you could put this formula in B1:

=TRIM(LEFT(A1,FIND(" (",A1)))

and fill down. If you wish to replace the original values, you can copy the column with the formulas, and use Paste Special with the Values option.

Reply | Quote

And here is a macro solution, assuming it’s column A:

Sub RemoveR()
Dim r As Long
Dim n As Long
n = Range("A65536").End(xlUp).Row
For r = 1 To n
Range("A" & r) = Trim(Left(Range("A" & r), InStr(Range("A" & r), " (")))
Next r
End Sub

Reply | Quote

Hi Hans,

Thank for looking into this and have provide not one but 2 solution both in macro and function.
The macro doesn’t exactly do what I expected because the column header and the text in cell A4 get deleted completely.
I tried to replace this line :
Range(“A” & r) = Trim(Left(Range(“A” & r), InStr(Range(“A” & r), ” (“)))
to
Range(“A” & r) = Trim(Left(Range(“A” & r), InStr(Range(“A” & r), ” (*)))
but its won’t works, pls advise

attached the sample.

thanks

regards, francis

Reply | Quote

Unlike the examples you provided, cell A4 doesn’t contain a space before the (
You can change the code like this:

Sub RemoveR()
Dim r As Long
Dim n As Long
n = Range("A65536").End(xlUp).Row
For r = 2 To n
Range("A" & r) = Trim(Left(Range("A" & r), InStr(Range("A" & r), "(") - 1))
Next r
End Sub

Or use Rory’s suggestion but enter (* (without a space) in the Find what box.

Reply | Quote

Hi Hans,

My apology that I didn’t mentioned that some data does not contain a space as I was concerned more on the differences in spacing so as to get the codes right.
This show that I still have a long long way to learn about marco. Upon seeing Rory’s reply, I tried to replace with the (* in the line of your code but its won’t work.

Thank for your help.

cheers, francis

Reply | Quote

Based on your example, you could select your data, choose Edit-Replace from the menu, enter " (*" without the quotes in the Find box (that’s a space, an opening parenthesis and an asterisk), leave the Replace box blank, and press Replace All.

Reply | Quote

Hi Rory,

This is a great way using Excel’s bulit-in function. I heard about using a formula on this but do not know about this bulit-in function and will not know the characters to use. Does MS have a list of characters which will tell you what each of the characters represent?

Thank for your guide.

cheers, francis

Reply | Quote

If you search for wildcards in the Excel help, you should get an overview. In fact, there are only three special characters:

? (question mark) stands for one arbitrary character; for example if you search for jo?n you’ll find john and joan but not johan.

* (asterisk) stands for any number of characters; for example if you search for jo* you’ll find john and jones and jonathan.

~ can be used in combination with ? and * to indicate that you want to search for ? or * itself instead of using a wildcard; for example you cna use who? if you want to search for the literal text who?

Reply | Quote