Taking Dashes out of a column

jbyrne · Feb 23rd, 2002, 10:18 AM

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 · Feb 23rd, 2002, 10:26 AM

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

Quote:

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 · Feb 23rd, 2002, 10:28 AM

Thanks for the formula!! Much appreciated.

Chris Davison · Feb 23rd, 2002, 11:09 AM

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 · Feb 23rd, 2002, 11:50 AM

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 · Feb 23rd, 2002, 11:52 AM

Hi

Ermm bit slow there ????

Ermm...

IML · Feb 24th, 2002, 03:58 PM

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 · Mar 5th, 2002, 11:29 AM

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

Feb 23rd, 2002, 11:52 AM	#6
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 23rd, 2002, 10:18 AM	#1
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	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.

Feb 23rd, 2002, 10:28 AM	#3
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	Thanks for the formula!! Much appreciated.

Feb 23rd, 2002, 11:09 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ]

Feb 23rd, 2002, 11:50 AM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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.

Feb 24th, 2002, 03:58 PM	#7
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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!!)

Mar 5th, 2002, 11:29 AM	#8
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	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!!