Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove space after # (Excel 2003)

    I have an Excel spreadsheet with a column of numbers that I copied from a web site. The numbers contain "$" signs and "commas" and a space following the number. I am able to get rid of the "$" signs and "commas" using the Find/Replace function under Edit, but I cannot get rid of the space following the number. I tried using "Find/Replace" to get rid of the space as well, but Excel comes back with "....cannot find a match". I've also tried using the Substitute and Trim functions followed by Paste Special "Values", but the space remains. The space following the number prevents Excel from recognizing it as a number, and therefore I cannot perform any mathematical functions or data sorting. I have attached a sample spreadsheet. Your help is appreciated.

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

    Re: Remove space after # (Excel 2003)

    Welcome to Woody's Lounge!

    The spaces are non-breaking spaces, with ANSI code 160. You can use a formula
    <code>
    =SUBSTITUTE(C5,CHAR(160),"")
    </code>
    in cell D5, fill it down as far as needed, than copy followed by paste special/values. If you want numeric values, use
    <code>
    =VALUE(SUBSTITUTE(C5,CHAR(160),""))
    </code>
    for the result of substitute is a text value.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Remove space after # (Excel 2003)

    You could also try the following.

    Instead of replacing a blank, in the in the Find What box, hold down the Alt key and enter 0160. You should then see something like a blank space. Replace all with nothing.

    Andrew C

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove space after # (Excel 2003)

    Hi JSax,

    See the attached for another method! Similar to Hans's approach!
    Using the formula : =LEFT(C5,LEN(C5)-1)*1
    Regards,
    Rudi

  5. #5
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    Thank you. I tried Hans' way since his reply was first, and that worked. Thanks for your replies and help.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks

    Perhaps you already realized this, but you can save a couple of steps and use the formula referencing column A instead of column C. It's not necessary to first get rid of the dollar signs or the commas through find and replace.

Posting Permissions

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