# Thread: Removing Hyphens From Numerical Data (2000)

1. ## 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. ## Re: Removing Hyphens From Numerical Data (2000)

Are there always 2 hyphens and are they always in the same position?

3. ## 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. ## Re: Removing Hyphens From Numerical Data (2000)

Always 4 hyphens and always in the same position.

Thanks.

5. ## Re: Removing Hyphens From Numerical Data (2000)

Great. Worked like a champ!!!

Thanks!!!

6. ## 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. ## 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. ## 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. ## 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. ## 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.

11. ## 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.

13. ## 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. ## 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.

15. ## Re: Removing Hyphens From Numerical Data (2000)

Here it is

Page 1 of 2 12 Last

#### Posting Permissions

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