How to remove dashes in Excel

Sometime while working with numeric data in Excel, we see that the data contains special formatting i.e. dashes and parentheses, because it is a phone number. There might be another case where we have to deal with social security numbers which contain dashes in between the numbers as shown above.

So in this tutorial we’ll learn some methods to remove the dashes from the data by following these steps.

Method 1 Use CTRL+E (Flash Fill) to remove dashes

Step 1 – Create a sample data and then use CTRL+E

• Create only one sample data set in the first row for both phone numbers and social security numbers and then use CTRL+E one by one in both columns.
• The desired data will be produced automatically as shown above.

Method 2 Use SUBSTITUTE to remove dashes

Step 1 – Create and implement the formula

• Excel’s built-in formula SUBSTITUTE can be used to remove any character from a string and in this case we’ll remove the dashes from the phone numbers and social security numbers. So use the following simple formula to replace the dashes with spaces or just remove the dashes and combine the numbers.

=SUBSTITUTE(A2,”-“,” “)

• In this case A2 represents the cell that contains the string from which dashes will be removed, second argument is “-” (dash) the character to be removed and the third argument is “ “ (blank space) which will replace the dash, or we can use “” to remove dashes and combine numbers as shown above.

Method 3 Use CTRL+H (Find and Replace) to remove dashes

Step 1 – Open Find and Replace Dialog using CTRL+H

• Excel’s another and very useful built-in feature i.e. Find and Replace can be used to replace the dashes very easily. Press CTRL+H, this will open up a new dialog box.
• Write a – in Find What and leave the Replace with option empty as shown above.

Step 2 – Use Replace All option to remove all dashes 

• Now press the Replace All button to remove all dashes at once. The beauty of this method is that we don’t need to create a new column for this. It will remove the dashes from the original data in its original place as shown above.