Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Blank Spaces in a number (2000)

    I have a long series of numbers (3000) that I have imported into Excel 2K from a data base. These numbers all include dashes ( 1-23456-789
    ) that I need to remove. I have done that with the SUBSTITUTE formula, =SUBSTITUTE(B2,"-"," ") , which works fine. But the formula leaves blank spaces where the dashes were. I now need to find the correct formula or formatting trick that will remove the blank spaces and leave the numbers as numbers and not text. And if possible can this be done in a single step?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Blank Spaces in a number (2000)

    Thom,

    If you can run the substitute formula again, try it without the space between the "".
    Try =SUBSTITUTE(B2,"-","")
    It worked for me.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Blank Spaces in a number (2000)

    Chuck, that works perfectly. I "Got It" right after I posted my question. Sometimes you just can't see the forest for the trees. I guess I was trying to make it harder than it needed to be.

    Thom

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Blank Spaces in a number (2000)

    =--SUBSTITUTE(B2,"-","")

    turns the result into a true number.
    Microsoft MVP - Excel

Posting Permissions

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