How to Remove Dashes in Excel Without Removing Zeros [5 Ways]

Sometimes we use datasets that contain dashes but we need those data without dashes. That’s why we need to remove them. But here we face some problems. When we try to remove dashes, some data containing zeros in the leading position is also deleted. Microsoft Excel automatically removes the leading zeros from a number. So our main goal is to remove those dashes but not the leading zeros.

1. Using the SUBSTITUTE Function to Remove Dashes in Excel Without Removing Zeros

To remove dashes in Excel without removing zeros we can use the SUBSTITUTE function. It is the easiest method. It is employed to swap out one character for another.

Syntax

SUBSTITUTE(text,old_text,new_text),old_text,new_text)

Formula

=SUBSTITUTE(C2,"-","")

Formula Breakdown

This formula utilizes the SUBSTITUTE function in Excel, targeting cell C2. It replaces hyphens (“-“) with an empty string (“”). In essence, it removes all hyphens from the content of cell C2.

To remove dashes without removing zeros in Excel use the SUBSTITUTE function. Follow the steps below:

1. Copy the formula above.
2. Apply the formula in cell D2.
3. Drag the Fill Handle from D2 to D10.

Final Result

Therefore, all dashes have been eliminated.

2. Using REPLACE Function to Remove Dashes in Excel Without Removing Zeros

The REPLACE function allows for the replacement of any character from any cell. We can use Excel’s REPLACE function to replace dashes but not remove zeros by following the instructions below.

Syntax

= REPLACE(old_text, start_num, num_chars, new_text)

Formula

=REPLACE(REPLACE(C2,4,1,""),6,1,"")

Formula Breakdown

This Excel formula employs the REPLACE function twice on cell C2. It first removes the character at the 4th position and then removes the character at the 6th position.

Essentially, it deletes characters at positions 4 and 6 in the content of cell C2, modifying the original text.

The REPLACE function in Excel can be used to remove dashes without removing zeros. Take the actions listed below:

1. Write the formula in cell D2  and apply it.
2. Drag from D2 to D10.

Final Result

Finally, we see that the REPLACE Function removes all dashes also it does not remove zeros in the first position of the string.

3. Using Flash Fill Feature to Remove Dashes in Excel Without Removing Zeros

The Flash Fill method is another method to remove dashes in Excel without removing zeros. But in this method, there is a little problem. Microsoft Excel can automatically remove the leading zeros. By following this method we can easily solve this problem.

Dashes can be eliminated in Excel using the Flash Fill feature, but zeros remain. Perform the following actions:

1. Select Column D2:D10 and then click the Right Button of the mouse.
2. Click Format Cells.
3. Click Text.
4. Choose a cell (D2) and manually enter 487824192 without using dashes.
5. Then select the next cell (D3).
6. Go to Data.
7. Then choose Flash Fill in the Data Tools section. or press CTRL+E.
8. First, click Warning Sign.
9. Then click Number Stored as Text and Ignore All.

Final Result

All dashes from the dataset have been removed by Flash Fill without removing zeroes.

4. Remove Dashes in Excel Without Removing Zeros Combining Multiple Functions

We can use a combination of multiple functions to remove dashes without removing zeros.

This process also makes use of the TEXTJOIN, IFERROR, INDIRECT, MID, and ROW functions.

Syntax

=TEXTJOIN(new_text, TRUE, IF(MID(text, ROW(INDIRECT("1:" & LEN(text))), 1) = "old_text", new_text, MID(text, ROW(INDIRECT("1:" & LEN(text))), 1)))

Formula

=TEXTJOIN("", TRUE, IF(MID(C2, ROW(INDIRECT("1:" & LEN(C2))), 1) = "-", "", MID(C2, ROW(INDIRECT("1:" & LEN(C2))), 1)))

Formula Breakdown

This formula in Excel utilizes the TEXTJOIN, MID, ROW, and IF functions.

• It iterates through each character in cell C2, excluding hyphens (“-“), and joins the remaining characters into a single text string.
• The ROW(INDIRECT(“1:” & LEN(C2))) generates an array of numbers from 1 to the length of C2, facilitating the examination of each character individually.
• The MID function extracts each character, and the IF condition checks for the presence of hyphens, excluding them from the final result.
• The TEXTJOIN function then concatenates the filtered characters into a coherent text string.

To remove dashes without removing zeros, utilize Excel’s TEXTJOIN, IFERROR, INDIRECT, MID, and ROW functions in combination. Perform the following actions:

1. Copy and apply the formula in cell D2.
2. Drag D2 to D10.

Final Result 

By using the combination of multiple functions we can easily remove dashes, without removing zeros,

5. Applying VBA Code to Remove Dashes in Excel Without Removing Zeros

The last method of this article is the use of VBA code to remove Dashes in Excel. The process is given below. By using VBA code we can remove dashes but the zeros that are present in the first can be also removed because of the formatting of the cell. We have to change the formatting of the cell to restore zeros.

Usually, the Cells are present in General format but we need to change it to Text format, so first zero is considered as text not number. To do this thing follow the process:

Usage guide

1. Select D2:D10 and then click the Right Button of the mouse.
2. Click Format Cells Option.
3. A new Dialogue Box will be opened and  Click Text. You can change your current cell format to any type of format you want. Then after that click OK.
4. Go Press ALT+F11 and open the Visual Basic Editor.
5. Next, choose Insert to create a Module.
6. Copy the following code and paste it to the newly built module.

   Sub RemoveDashesPreserveLeadingZeros()
   Dim rng As Range
   Dim cell As Range
   Dim outputCell As Range
   Dim result As String
   
   ' Prompt the user to select a range
   On Error Resume Next
   Set rng = Application.InputBox("Select a range with dashes to process:", Type:=8)
   On Error GoTo 0
   
   ' Exit if the user cancels the input box
   If rng Is Nothing Then
        Exit Sub
   End If
   
   ' Prompt the user to select a cell for the output results
   On Error Resume Next
   Set outputCell = Application.InputBox("Select a cell for the output results:", Type:=8)
   On Error GoTo 0
   
   ' Exit if the user cancels the input box
   If outputCell Is Nothing Then
        Exit Sub
   End If
   ' Loop through each cell in the selected range
   For Each cell In rng
        result = Replace(cell.Value, "-", "") ' Remove dashes
        ' Check if the original cell had leading zeros and add them back
        If Len(cell.Value) > 0 And Left(cell.Value, 1) = "0" Then
            Do While Left(result, 1) = "0" And Len(result) > 1
                result = Right(result, Len(result) - 1)
            Loop
            result = "0" & result ' Add leading zeros back
        End If
        ' Place the modified value in the output cell
        outputCell.Value = result
        ' Move to the next output cell
        Set outputCell = outputCell.Offset(1, 0)
   Next cell
   End Sub

7. Save the code by pressing CTRL+S.
8. Go to Developer and click the Macros button or you can easily open the Macro dialog box by pressing ALT+F8.
9. Click Run by selecting RemoveDashesPreserveLeadingZeros.
10. Select the range C2:C10 and click OK.
11. Select cell D2 and click OK.
12. After that, a warning sign will appear beside D2:D10. Click the warning sign, it will show us if we want to Store it as text or Convert it as a Number. If we click Stored as text the number will be saved as text and the leading zeros are present in the text. If we Convert as a number, it will be saved as a number, and zeros in the leading position will be removed. So, firstly click, Stored as Text, then click Ignore All.

Final Result 

Finally, we see that we can remove dashes using VBA code also it will preserve leading zeros.

Conclusion

In this article, we have discussed 5 methods to remove dashes in Excel without removing zeros. The SUBSTITUTE, REPLACE, and Combination of Functions easily remove dashes and preserve zeros. However, the VBA method and Flash Fill method can not easily preserve zeros though they can easily remove dashes. For this type of problem, we need to convert the Number to Text first. Then we can easily remove dashes and preserve zeros.

Frequently Asked Questions (FAQs)

How do I get rid of dash lines in Excel?

To remove dash lines in Excel, you can use the SUBSTITUTE function to replace the dash lines with an empty string. Here’s a generic formula: =SUBSTITUTE(A1, “-“, “”)

1. Replace “A1” with the reference to the cell containing the text with dash lines.
2. This formula will replace all occurrences of the dash (“-“) with an empty string, effectively removing the dash lines from the content of the cell.
3. Adjust the cell reference based on where your data is located.

How do I get Excel to show zero instead of dash?

To make Excel show zero instead of a dash for cells with zero values, you can use the following formula: =IF(A1=0, 0, A1)

1. Replace “A1” with the reference to the cell you want to modify.
2. This formula checks if the value in cell A1 is zero.
3. If it is, it displays 0; otherwise, it displays the original value. This way, you’ll see zero instead of a dash for cells with zero values.
4. Adjust the cell reference based on where your data is located.

How do I make Excel not remove zeros?

If you want to prevent Excel from automatically removing leading zeros in a cell, you can format the cell as text before entering the data. Here’s how:

1. Select the cell or range of cells where you want to enter or preserve leading zeros.
2. Right-click on the selected cell(s) and choose “Format Cells.”
3. In the Format Cells dialog box, go to the “Number” tab.
4. Select “Text” from the category list.
5. Click “OK” to apply the changes.

Now, when you enter data into the selected cell(s), Excel will treat the entry as text, and leading zeros will not be automatically removed.

If you’ve already entered data and leading zeros have been removed, you may need to re-enter the data after formatting the cells as text to preserve the leading zeros.

How do you keep leading zeros in sheets?

To keep leading zeros in Google Sheets, you can follow these steps:

1. Select the cells or range of cells where you want to keep leading zeros.
2. Right-click on the selected cells and choose “Format cells.”
3. In the Format Cells panel, go to the “Number” tab.
4. Select “Plain Text” from the options.
5. After formatting the cells as plain text, enter your data.
6. The leading zeros should now be preserved, and Google Sheets will not automatically remove them.

Remember that formatting as plain text should be done before entering data to ensure that leading zeros are not removed. If data entry has already occurred, and the system has removed leading zeros, you may need to re-enter the data after formatting the cells as plain text.