Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Characters (Office 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Characters (Office 2003)

    If your data are in A1, A2 etc., you could put this formula in B1:
    <code>
    =TRIM(LEFT(A1,FIND(" (",A1)))
    </code>
    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Characters (Office 2003)

    And here is a macro solution, assuming it's column A:
    <code>
    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</code>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Remove Characters (Office 2003)

    Based on your example, you could select your data, choose Edit-Replace from the menu, enter <code>" (*"</code> 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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Characters (Office 2003)

    Unlike the examples you provided, cell A4 doesn't contain a space before the (
    You can change the code like this:
    <code>
    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
    </code>
    Or use Rory's suggestion but enter <code>(*</code> (without a space) in the Find what box.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Characters (Office 2003)

    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
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Characters (Office 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Characters (Office 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Characters (Office 2003)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •