Taking Dashes out of a column

jbyrne

Board Regular
Joined
Feb 22, 2002
Messages
178
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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
 
Upvote 0
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.

:)
 
Upvote 0
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!!)
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top