Formula to Delete hyphens

mcorydon

New Member
Joined
Jan 6, 2014
Messages
16
This is probably the easiest question you will answer today but I'm trying to create a formula that turns 123-456-7890 into 1234567890. Ideally this is a formula that i can drag down my entire spread sheet to change a few hunder fax number.

Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
This is probably the easiest question you will answer today but I'm trying to create a formula that turns 123-456-7890 into 1234567890. Ideally this is a formula that i can drag down my entire spread sheet to change a few hunder fax number.

Give this a try...

=SUBSTITUTE(A1,"-","")
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,292
Office Version
  1. 2013
Platform
  1. Windows
mcroydon,

Eg original in A1 this formula in B1....

=SUBSTITUTE(A1,"-","")

Hope that helps.
 

Audiman

New Member
Joined
Sep 10, 2009
Messages
31

ADVERTISEMENT

Just a suggestion but I think people are making this more complicated than it needs to be.
Why not just select the Column and press CTRL+H
Find Replace - with nothing.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Just a suggestion but I think people are making this more complicated than it needs to be.
Why not just select the Column and press CTRL+H
Find Replace - with nothing.
The OP said "I'm trying to create a formula that ... i can drag down my entire spread sheet"... my thought was he wanted to preserve the original data and that the dashless numbers were needed for some other formula's or program's use.
 

Audiman

New Member
Joined
Sep 10, 2009
Messages
31
Sorry I wasn't trying to imply anything. Just sometimes people over think stuff. I have actually done the Substitute to delete all the * in a sheet. When I could have just used CTRL+H. Just over thought the process. I had believed even the OP over thought it.
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
Its always worth suggesting, so the OP can decide if they want that or not But​ using Substitute is not over complicating it, its just what was asked for

Sorry I wasn't trying to imply anything. Just sometimes people over think stuff. I have actually done the Substitute to delete all the * in a sheet. When I could have just used CTRL+H. Just over thought the process. I had believed even the OP over thought it.
 

Forum statistics

Threads
1,137,060
Messages
5,679,382
Members
419,824
Latest member
Mercy kiara

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
Top