Taking Dashes out of a column
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,169
    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,169
    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!!

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
  •  

 

 
DMCA.com