If you’ve ever worked in an HR role, you know how challenging it can be to maintain a large volume of personal details for multiple employees, including their CNIC numbers, IBANs, Passport numbers, or even cellphone numbers.
All of these numbers are often accompanied by dashes that may appear in the center, at the beginning, or the end of the number.
Manually removing the dashes is definitely not an option as this could take hours or even days if dealing with a substantial amount of data. Additionally, there is a high chance of data loss or errors due to accidental backspacing and clicks.
Table of Contents
3 Methods to Remove Dashes in Excel
1. Find and Replace Function
The very well-known ‘Find and Replace’ function can help you remove dashes from Excel only in a few clicks. Take a look below to see how.
The data above represents the IBANs for a list of employees. As evident, these numbers are paired with multiple dashes that must be removed.
To do so, simply select the Column containing the dashes (i.e. Column B) and go to the Find and Replace function as follows.
Home > Editing > Find & Select > Replace
Pro Tip: Press Ctrl + H to instantly open up the Find and Replace dialogue box in Excel.
Against ‘Find’, enter dashes (-), whereas, leave the box against ‘Replace’ vacant as we want the dashes to be removed.
Hit ‘Replace All’ to have the results as follows.
Related Tutorial: How to Remove Commas in Excel
2. The Substitute Function
Next on the list is the Substitute Function that works on the pattern of the ‘Find and Replace’ Function. Syntax of the Substitute Function reads as follows.
=SUBSTITUTE (text, old_text, new_text, [instance])
- Text: The text where changes are to be made.
- Old_text: The text to be replaced
- New_text: The text to be replaced with
- Instance: Optional. The instance where changes are made. All instances are replaced if omitted.
You may access the Substitute function from the Functions Library as follows.
Home > Editing > Find & Select > Replace
In the example given above, to use the substitute function to remove dashes from Employee IBANs in Excel, compose it as follows.
=SUBSTITUTE (B2, “-“, “”)
Where B2 defines the cell containing dashes. The Old text is set as “-“, and the new text is left blank “” as we want the dashes to be replaced with nothing but removed.
Note: Both the strings for the Old and New text must be enclosed in double quotation marks.
Hit Enter to see the results and drag the fill-handle to yield similar results against the whole list of IBANs.
Related Tutorial: How to Remove Leading Zeroes in Excel
3. Kutools
The third and last option on this list involves Kutools. Putting in formulas to remove dashes may prove a little hectic and technical for new Excel users. Kutools offer an in-built tool for character removal from Excel.
Continuing the same example as above, to remove dashes between IBANs from Excel, follow these simple steps.
Step 1:
Select the Column containing the dashes i.e. Column B and then,
Go to Kutools > Text > Remove Characters
This opens up the ‘Remove characters’ window, as shown below.
Step 2:
Under the option to ‘Remove characters’, choose Custom and select dash (-) from the dropdown menu.
Take a look at the data post-dashes removal through the Preview Pane on the right and Hit ‘Okay’ if everything looks good.
Excel filters out dashes from the selected range, as shown below.
Note: Kutools is an add-in to Excel. If you do not see the tabs ‘Kutools’ or ‘Kutools Plus’ on your Excel Ribbon, you probably do not have it installed in your Excel. But no worries, you can easily download it from the web and have it added to your Excel.
Bottom Line
Try either of the above methods to remove unwanted dashes from your dataset in Excel. Or try installing Kutools to ease the job through an in-built character removal function.
Suggested Tutorial: How to Strikethrough in Excel?