Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Taking Dashes out of a column

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me the formula to remove dashes a series of numbers. For example, removing the dashes from a list of social security numbers or the dashes from a list of phone numbers. Any help would be much appreciated.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First, make sure it is not just a custom format by changing the format to general. If they still exist, you could put the following formula in a column
    =SUBSTITUTE(A1,"-","")
    in say B1, and copy it down to the end of your list.
    Now, copy paste special values over column B and you can delete your original column.

    Good luck


    On 2002-02-23 09:18, jbyrne wrote:
    Can anyone tell me the formula to remove dashes a series of numbers. For example, removing the dashes from a list of social security numbers or the dashes from a list of phone numbers. Any help would be much appreciated.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the formula!! Much appreciated.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IML.... if we used the edit/replace facility, replacing "-" with "" would this still retain the integrity of the numbers ?

    I only ask as it seems a touch more efficient than having to add a column and then copy/paste special.

    Of course though, I'm always learning !

    =====================

    edit

    the question was for a formula, not a method.... of which yours was bang on


    Chris


    [ This Message was edited by: Chris Davison on 2002-02-23 10:11 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi---

    Can you not simply Find and replace

    Find -
    Replace

    Do not enter in replace and replace ALL

    That will stop messing about as you have no convertion to do just remove the - this will protect the data also

    Woth a hit at it i guess.


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Ermm bit slow there ????

    Ermm...


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jack / Chris,
    I agree with both of you, find and replace would be the best solution. I should have suggested that, but maybe I did get a little tunnel vision from formula being in the question (which is odd, since usually I answer a different question than is asked anyway!!)

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the formula and the replace function. I'm pretty new to excel and love the help I get from this site. Thanks again to all!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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