How to Remove Dashes in Excel

In Excel, it is often necessary to remove dashes from data fields. Dashes can be problematic because they are considered non-numeric characters and can cause issues when performing calculations or sorting data.

Here are a few reasons why removing dashes in Excel can be helpful:

1. To make the data consistent: When working with large datasets, it’s important to ensure that the data is consistent. Removing dashes helps to standardize the data and makes it easier to work with.
2. To enable calculations: As mentioned above, dashes are considered non-numeric characters, which means that they cannot be used in calculations. By removing them, you’ll be able to perform calculations on your data more easily.
3. To improve data visualization: When creating charts and graphs in Excel, having consistent data is important for accurate representation. Removing dashes helps to ensure that there are no outliers in the data that could skew the results.

Remove dashes in excel without removing zeros

To remove dashes from Excel cells without removing zeros, you can use a combination of the SUBSTITUTE and IF functions. Here’s how:

1. Select the range of cells containing the data you want to edit.
2. Right-click on the selection and choose “Format Cells” from the context menu.
3. In the “Number” tab, select “Custom” and enter the following format: 0;-0;;@
4. Click “OK” to close the Format Cells dialog box.

This format will display zeros as well as positive and negative values, but it will leave blank cells empty.

5. In an adjacent column, enter the following formula in the first cell: =IF(ISNUMBER(A1),A1,SUBSTITUTE(A1,”-“,””))
6. Replace “A1” with the reference to the first cell in your selected data range, if necessary.
7. Copy the formula down to apply it to all cells in the adjacent column.

The formula checks whether each cell in the original data range contains a number. If it does, the formula simply copies the value. If not, it uses the SUBSTITUTE function to replace any dashes with an empty string.

This method will remove dashes from your data without affecting zeros or any other numbers in the cells.

Remove dashes in Excel using formula

To remove dashes from Excel cells using a formula, you can use the SUBSTITUTE function. Here’s how:

1. Select the cell where you want to display the edited data.
2. Enter the following formula: =SUBSTITUTE(A1,”-“,””)
3. Replace “A1” with the reference to the cell containing the original data.
4. Press Enter to apply the formula.

The SUBSTITUTE function searches for the dash character “-” and replaces it with an empty string “”. This will remove all dashes from the selected cell.

5. Copy the formula down to apply it to all cells in the column.

This method is useful if you only need to edit a small number of cells or if you want to create a new column with the edited data. If you want to edit the original data range directly, you can use the “Find and Replace” function as described earlier.

Remove dashes in Excel shortcut

To remove dashes in Excel using a shortcut, you can use the “Find and Replace” function. Here’s how:

1. Select the range of cells containing the data you want to edit.
2. Press Ctrl + H to open the “Find and Replace” dialog box.
3. In the “Find what” field, enter “-” (without the quotes).
4. Leave the “Replace with” field blank.
5. Click on the “Options” button to expand the options.
6. Make sure the “Match entire cell contents” option is unchecked.
7. Click on “Replace All”.

This will remove all dashes from your selected data range.

Note that this method will also remove any leading or trailing spaces in the cells, so make sure you don’t need those before using this shortcut.

You can also create a custom keyboard shortcut for the “Find and Replace” function by going to File > Options > Quick Access Toolbar. From there, click on “Customize Ribbon” and then “Customize…” next to “Keyboard shortcuts”. In the “Categories” box, select “Commands Not in the Ribbon”, and then scroll down and select “EditReplace”. You can then assign a custom keyboard shortcut to this command.

Remove dashes from ssn

To remove dashes from an SSN (Social Security Number) in Excel, you can use a combination of the RIGHT, LEFT, and SUBSTITUTE functions. Here’s how:

1. Select the data range containing the SSNs.
2. Insert a new column to the right of the SSN column by right-clicking on the column header and selecting “Insert”.
3. In cell B1 (assuming your SSN data starts in cell A1), enter the following formula: =LEFT(A1,3)&”-“&MID(A1,4,2)&”-“&RIGHT(A1,4)

This formula adds dashes to the SSN to create the standard format with three groups of numbers separated by hyphens. The LEFT function extracts the first three digits, the MID function extracts the next two digits starting at the fourth position, and the RIGHT function extracts the final four digits.

4. Copy the formula down to apply it to all cells in the column.
5. Select the entire column B, then press Ctrl + C to copy it.
6. Right-click on the original SSN column (column A) and select “Paste Special”.
7. In the “Paste Special” dialog box, select “Values” and click “OK”.

This will replace the original SSN values with the edited values that include hyphens.

8. Delete the temporary column (column B) by right-clicking on the column header and selecting “Delete”.

This method preserves the original SSN values while creating a new column with the formatted values. If you don’t need to preserve the original values, you can skip step 5 and simply delete the original column after pasting the formatted values.

Remove dashes in excel with substitute function

To remove dashes from a cell in Excel using the SUBSTITUTE function, you can use the following formula:

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

This formula replaces all instances of the dash “-” with an empty string “” in cell A1.

To apply this formula to multiple cells at once, you can select the range of cells you want to edit and enter the formula into the first cell. Then, copy and paste the formula into the rest of the cells in the range.

If you want to edit the original cells directly (instead of creating a new column), you can use the “Find and Replace” feature as described earlier. To do this:

1. Select the range of cells containing the data you want to edit.
2. Press Ctrl + H to open the “Find and Replace” dialog box.
3. In the “Find what” field, enter “-” (without the quotes).
4. In the “Replace with” field, leave it blank.
5. Click on “Replace All”.

This will remove all dashes from the selected data range.

Remove dashes from phone numbers in Excel

To remove dashes from phone numbers in Excel, you can use a combination of the SUBSTITUTE and CLEAN functions. Here’s how:

1. Select the range of cells containing the phone numbers you want to edit.
2. Insert a new column to the right of the phone number column by right-clicking on the column header and selecting “Insert”.
3. In cell B1 (assuming your phone number data starts in cell A1), enter the following formula: =SUBSTITUTE(A1,”-“,””)

This formula replaces all instances of the dash “-” with an empty string “” in cell A1, effectively removing the dashes.

4. Copy the formula down to apply it to all cells in the column.
5. In cell C1, enter the following formula: =CLEAN(B1)

The CLEAN function removes all non-printable characters from the cell contents, including any spaces or special characters that may have been left behind after removing the dashes.

6. Copy the formula down to apply it to all cells in the column.
7. Select the entire column C, then press Ctrl + C to copy it.
8. Right-click on the original phone number column (column A) and select “Paste Special”.
9. In the “Paste Special” dialog box, select “Values” and click “OK”.

This will replace the original phone number values with the edited values that have had their dashes removed and any non-printable characters cleaned up.

10. Delete the temporary columns (columns B and C) by right-clicking on the column headers and selecting “Delete”.

This method preserves the original phone number values while creating a new column with the edited values. If you don’t need to preserve the original values, you can skip step 7 and simply delete the original column after pasting the formatted values.

Remove dashes from dates in Excel

To remove dashes from dates in Excel, you can use the SUBSTITUTE function. Here’s how:

1. Select the range of cells containing the dates you want to edit.
2. Insert a new column to the right of the date column by right-clicking on the column header and selecting “Insert”.
3. In cell B1 (assuming your date data starts in cell A1), enter the following formula: =SUBSTITUTE(A1,”-“,””)

This formula replaces all instances of the dash “-” with an empty string “” in cell A1, effectively removing the dashes.

4. Copy the formula down to apply it to all cells in the column.
5. In cell C1, enter the following formula: =DATEVALUE(B1)

The DATEVALUE function converts the text string in cell B1 into a serial number that Excel recognizes as a date value.

6. Copy the formula down to apply it to all cells in the column.
7. Select the entire column C, then press Ctrl + C to copy it.
8. Right-click on the original date column (column A) and select “Paste Special”.
9. In the “Paste Special” dialog box, select “Values” and click “OK”.

This will replace the original date values with the edited values that have had their dashes removed and converted into proper date values.

10. Delete the temporary columns (columns B and C) by right-clicking on the column headers and selecting “Delete”.

This method preserves the original date values while creating a new column with the edited values. If you don’t need to preserve the original values, you can skip step 7 and simply delete the original column after pasting the formatted values.

rmatted values.

Use VBA Codes to Remove all the Dashes

here’s a VBA code that will remove all dashes (“-“) from a string:

Function RemoveDashes(inputString As String) As String
    RemoveDashes = Replace(inputString, "-", "")
End Function

To use this function, simply pass the string you want to remove dashes from as an argument. For example:

Dim myString As String
myString = "123-456-7890"
myString = RemoveDashes(myString)

After running this code, the variable myString will contain the value “1234567890” with all dashes removed.

Remove Dashes Using Power Query

To remove dashes using Power Query, you can follow these steps:

1. Open Power Query by clicking on the “Data” tab in Excel and then selecting “From Table/Range”.
2. In the Power Query Editor, select the column that contains the dashes.
3. Click on the “Transform” tab and select “Replace Values” from the dropdown menu.
4. In the “Replace Values” dialog box, enter a dash “-” in the “Value To Find” field and leave the “Replace With” field blank.
5. Click “OK” to close the dialog box and apply the changes.

This will replace all dashes in the selected column with blank cells. To remove the blank cells, you can right-click on the column header and select “Remove Empty” from the dropdown menu.

How do you change a dash to 0 in Excel?

To change a dash to 0 in Excel, you can use the Find and Replace function. Here are the steps:

1. Select the range of cells that contain the dashes you want to replace with zeros.
2. Press “Ctrl + H” on your keyboard to open the Find and Replace dialog box.
3. In the “Find what” field, type a single dash (-).
4. In the “Replace with” field, type the number 0 (zero).
5. Click the “Replace All” button.

Excel will replace all dashes in the selected range with zeros.

Related posts:

Excel SEARCH Function Excel PROPER Function Keyboard Shortcuts for Applying Borders in Excel Excel CHAR Function