How to Remove Dashes in Excel

How to Remove Dashes in Excel: All You Need to Know

by

Suppose, you received a ton of data in an excel sheet. These could be ISBN numbers, phone numbers, or zip codes. In many cases you’ll find all these data come with included dashes. You might want to remove them for your own ease.

Now hold up! Before the idea of removing all those dashes manually from your worksheet come to your head, let me tell you this. There are various other methods you can choose from to get things done faster and easier. That’s exactly what we’re about to talk about in this writeup.

So, if you’re interested to know about everything there is to know about removing dashes in Excel, you’ve come to the right place. We’re going to go through each procedure step by step.

How to Do It

I could just tell you to do “this and that” and your work will be done. But it’s better to know all the methods there are. Who knows? You might find one to be easier than the others. So, here we go.

Find and Replace

Let’s talk about how you can remove those pesky dashes using the find and replace method. This procedure will firstly help you find all the dashes there are in the excel sheet. Then all you’ll need to do is to replace it with a null string or a space character.

Okay, so let’s do this step by step.  As you can see inside the SSN data set, there are quite a number of dashes. Here’s how to remove them.

1. The first thing you want to do is, select the data set that you want to remove the dashes from.

2. Then you’ll want to press Ctrl and H. Then you’ll see the find and replace dialogue box pop up.

Find and replace 1

3. After this, you’ll need to type the dash symbol in the “Find what” field.

4. Keep the “Replace with” field empty.

5. Click on the replace all button, and you’re good to go.

Find and replace 2

6. Follow the steps properly and you’ll be left with the numbers only and all the dashes will be removed.

This procedure is really easy to pull off in Excel. Here’s a couple of things you might want to know before we move on to the next step.

  • You might want to save your data beforehand before moving forward with this procedure as it changes the original data.
  • If there are zeroes before a particular number, after applying this method, the zeroes will be removed. Suppose there’s a number with “03” or “003” in the beginning. After applying this method, the number will begin with “3” in both cases.

Formula

Here we are with yet another simple method to remove dashes from excel. Suppose the SSN data looks somewhat like the image below and you want to remove the dashes from the numbers accordingly.

The Excel formula below should do the trick:

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

The substitute formula requires three arguments as stated below.

  1. Firstly, you have to type in the cell number that you want to replace the text string from (Dashes in this particular case).
  2. Then you’ll have to type the string in double quotes. “-“ in this case to be exact.
  3. Then you have to type the string you want to replace it with. Null string or “ “ in this case.

Through Excel formula

Find and Select

This will be our third method in the list. Let’s look at how you can pull it off with ease.

1. Firstly, you need to go to the Home tab of excel and then select the “Find and sect” option. Under that, you’ll need to select “Replace.”

2. Then, go to the replace dialogue box. Within the “Find What” box type, you’ll have to type the – (Dash) symbol. And of course, in the replace with box you’ll have to write Null ( ). Then just select Find all. (You can type Ctrl + H to bring out the find and replace window as well.

Find and Select 1

3. Once you’ve found all the dashes, it’s time to select replace all. Click Ok for confirmation and you’re done.

Find and Select 2

4. After you’ve done that, all the dashes and hyphens should be removed.

5. Now that wasn’t too hard now, was it?

Format Cell

Here we are with the format call method. This’ll allow you to get the job done super-fast. So’ let’s go through it step by step. Shall we?

1. The first thing you want to do is, select the range of cells you want to remove the dashes from.

2. Then you’ll want to select format. After that, you’ll have to go to “Format cells.”

The format call method 1

3. Go to the custom category of the format cells window.

4. Then you’ll need to modify any format with nine 0’s (It’ll look something like this: 000000000).

The format call method 2

5. That’s pretty much it. Press Ok for confirmation and it should be complete.

The VBA Method

For this, firstly, you’ll need to open the Microsoft Visual Basic model and run a code. So, here’s how it goes.

1. Press Alt + F11 to open Microsoft VBA.

2. In the toolbar, go to insert and press Module.

3. Paste the following code in the module:

Sub DeleteDashes()

Dim rng As Range

Dim WorkRng As Range

On Error Resume Next

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False

For Each rng In WorkRng

rng.NumberFormat = “@”

rng.Value = VBA.Replace(rng.Value, “-“, “”)

Next

Application.ScreenUpdating = True

End Sub

The VBA Method 1

4. Then you’ll have to press f5 to run the code.

5. A selection window will pop up for you to decide the range of cells you want to remove the dashes from.

The VBA Method 2

6. Press Ok and you’re done.

Using Kutools

The Kutool plugin for excel is yet another great option for removing dashes and hyphens from a spreadsheet. Editing and removing characters from the document becomes super easy with this one. The instructions you can follow to get it done are given below.

  1. The first thing you’ll have to do is download Kutools and install the plugin properly.
  2. Open the Excel file you want to work on and then select the Kutool plugin.
  3. Select the cells you want to remove the dashes from by clicking and dragging.
  4. Once you’re done with that, select the Text button on Kutools at the top. The button sort of looks like a capital T.
  5. From the dropdown menu, select remove characters.
  6. A dialog box will open. From that, you’ll need to select Custom, and there you have to insert Dash.
  7. Press Ok and voila! All the dashes you wanted to remove will disappear.

Some Tips That’ll Help Out

While you’re following one of the procedures that I’ve mentioned beforehand, you might want to follow a few simple tips that’ll help you out.

  • Create backups for your document. Just in case the procedure gets messed up, you’ll still have access to your previous data.
  • As I mentioned before, once you remove the dashes from spreadsheet, the zeroes in the beginning of the numbers might get removed as well. You might want to change the cell format to prevent this from happening.
  • Save your document on the go. After making a specific change, it’s quite convenient if you keep the document saved. Be it manual or auto save, it should help you out.
  • If you have problems going to the Find and Replace dialog box manually, you can press Ctrl + H as a shortcut.

Why Removing Dashes Help Out

Let’s take a look into why removing dashes is a thing while using Excel and why many users actually need to do it.

  1. It offers a deeper insight into the data and removes distractions from data consumption.
  2. A worksheet without dashes might seem rather clean and offer a more professional appearance overall.
  3. The procedures learnt to remove dashes give a better insight to Microsoft Excel and how it functions overall.

Final Words

Now that we’ve gone through all the details about how to remove dashes from Excel, it’s entirely up to you to decide which method you’ll use over the others. Some procedures seem simple to some and the others seem a bit complicated. It’s all about personal perspective in the end.

So, that’s pretty much it. I hope all the information helped you out. I bet you’re pretty much clear about how to remove dashes from excel at this point. Farewell for now!

Similar Post:

Leave a Comment