Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Hyphens From Numerical Data (2000)

    Hi Loungers, I am wanting to know if there is a way to remove hyphens "-" from numerical data. For example, from SS#'s would it be possible to remove the hyphens:

    Example: Before: 111-11-1111
    After: 111111111

    All suggestions are greatly appreciated!

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Are there always 2 hyphens and are they always in the same position?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Removing Hyphens From Numerical Data (2000)

    Are these "real" numbers with a special format applied, or are they stored as text? You can check by looking in the formula bar to see if the hyphens are displayed there.
    If no, they are numbers. Just set the number format (Format | Cells...) to General.
    If yes, they are text values. You can use Edit | Replace to replace hyphens by nothing.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Always 4 hyphens and always in the same position.

    Thanks.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Great. Worked like a champ!!!

    Thanks!!!

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    HansV, I noticed a problem after the last post. The first number (leftmost) is always a 0. When I do this, the 0 is lost.

    For example, what was 04-99 now becomes 499.

    Any way of preventing this or rectifying?

  7. #7
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    I thought of a formula solution too -
    Maybe it could be used if the actual value of the cell needs to be obtained from another formula - for a lookup list or something...

    =IF(N(A5) = 0,LEFT(A5,3)&MID(A5,5,2)&RIGHT(A5,4),A5)

    Chris
    Chris

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

    Re: Removing Hyphens From Numerical Data (2000)

    Set the number format of the cells to Text before removing the hyphens. (Hopefully, you can use Undo to restore the original situation, otherwise close the workbook without saving, and reopen it.)

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Hans, I think this is what I did... But anyway retried and here is what happened.

    1. Converted column to text (example cell 03-89-1111-3040-780).
    2. Did the edit, find, replace and hyphens are gone, but now the data is in scientific notation. For instance 3.89E13.

    I want to see 03891113040780...

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Then the cell format is NOT Text, it is either General or a number format. Use Format/Cells to change the format to Text before removing the dashes.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Legare, the format IS text. Here is the process I am following:
    1. Highlight all column.
    2. Select format.
    3. Select cells.
    4. On Number tab, select Text then OK.
    5. With column highlighted, select, Edit, Find, Replace, OK

    Then the 0 is lost and the number appears in scientfic notation. I go back to the cell and select format, cells and the word text is highlighted which makes me think it IS text... Also, by default text is usually left-aligned and this is left aligned as well.

    Any other ideas?

    Thanks.

  12. #12
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Try making your column wider

  13. #13
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    I have tried. NO help; however, when I look up on the toolbar beside the equal sign I see the string of numbers, but the 0 is missing.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Would it be possible to remove everything else but this column from a copy of the Workbook and upload it? The version with the hyphens still in the numbers.
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing Hyphens From Numerical Data (2000)

    Here it is

Page 1 of 2 12 LastLast

Posting Permissions

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