Taking Dashes out of a column

[jbyrne]

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.

 

[IML]

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.

 

[jbyrne]

Thanks for the formula!! Much appreciated.

 

[Chris Davison]

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

 

[Jack in the UK]

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.

 

[Jack in the UK]

Hi

Ermm bit slow there ????

Ermm...

 

[IML]

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!!)

 

[jbyrne]

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!!