If you’re transferring a form into a spreadsheet, you may run into formatting problems from time to time. Fortunately, Excel features several handy tools that will allow you to clean everything up in no time.
One way to remove dashes in Excel is to take advantage of functions:
- First, select an empty cell
- Then, enter the function
=SUBSTITUTE(CELL,"-","") - After, replace “CELL” with the coordinate for your target cell
- Finally, hit the “Enter” key
Another way you can remove dashes in Excel is to run a VB script (linked here):
- First, select the cells you’d like to affect
- Then, open the VB interface with
Alt+F11 - After, select “Module” under “Insert”
- Finally, paste the script and hit “F5”
- You may need to select “OK” in another window
The final method to remove dashes in Excel is to use Kutool’s character removal tool (this will require you to purchase and install Kutools for up to $49).
- First, select the cells you’d like to affect
- Then, head to the Kutools tab and open the “Text” dropdown
- After, select “Remove Characters…” and check the “Custom” box
- Finally, input a “-” in the custom field and click “Apply” followed by “OK”
Removing dashes from Excel is reasonably straightforward. There are two native methods to choose from; however, you can also use another solution that involves a third-party plugin. You can refer to the screenshots in each step if you encounter any difficulties. You can also leave a comment at the bottom of the post for more information.
In the list below, you will find this post’s various topics. If you’re not interested in each topic or you already know what you’re looking for, you can take advantage of the following links to access relevant content quickly.
- How to remove dashes in Excel (functions)
- How to remove dashes in Excel (VB scripts)
- Removing characters in Excel (Kutools)
- Blog post recap
How to remove dashes in Excel (functions)
Excel beginners should be familiar with functions and formulas— tools that enable us to automate our calculations and manipulate our data. It turns out you can use them for formatting too!
Step 1 — Launch Microsoft Excel
If you’d like to follow along with the native application, tap the “Start” key and type “Excel.” Then, hit the “Enter” key. Otherwise, launch the web service via the following link.
Step 2 — Input the provided function
Enter an available cell and enter the function provided but don’t hit “Enter” immediately. You need to replace the “CELL” part with your target cell. In the example below, I use “G2.”
=SUBSTITUTE(CELL,"-","")
Bonus — Bulk application
Select the cell you initially edited. You should spot a square in the bottom right corner of the cell. Click hold and drag the square to recreate the effects of your function in the cells you mark.
How to remove dashes in Excel (VB scripts)
VB scripts enable Excel user’s to automate and simplify their Excel experience by exploiting the computer scripting language Visual Basic. Fortunately for us, someone else has already provided the code.
Step 1 — Open the VB interface for Excel
Start by selecting the cells you want to edit and then open the VB interface. You can launch the VB interface via the Alt+F11 shortcut. I have provided a screenshot of the shortcut’s keys for your convenience.
Step 2 — Create a VB script module
Now you’ve accessed the VB scripting interface, wherein we can create a VB module. To do so, open the “Insert” menu and select “Module.”
Step 3 — Run the VB script
Below I have provided a script you can use, copy and paste the script into the module. Afterward, you can run the script by tapping “F5” and selecting “OK.”
Sub DeleteDashes()
'Update 20130907
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
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
Removing characters in Excel (Kutools)
Kutools is a third-party add-on for Excel credited for featuring over 300 additional features. While some are advanced, others can be notably minor or even insignificant— like the small tool we’ll use in the final guide. Unfortunately, Kutools for Excel is a paid add-in costing upwards of 42 USD. However, you can download the program here and exploit its free thirty-day trial if you want to try it.
If you have no intention of using Kutools’ additional features, I recommend you instead use an alternate solution.
Step 1 — Get started with Kutools
Once more, start by selecting the cells you want to edit. When you’re ready, head to the “Kutools” tab. Find and open the “Text” dropdown under “Editing.” In the menu, select “Remove Characters…“
Step 2 — Setup and run the tool
Within the tool’s window, you should spot a box labeled “Custom.” Ensure you check its adjacent checkbox— it will unlock a text field. Use the text field to input characters you’d like to remove. Take the “-” character, for instance. Finally, select “Apply” followed by “OK.”
Blog post recap
Microsoft Excel features two native text formatting tools you can use to replace or remove characters from cells. However, an additional method exists wherein a user can exploit third-party add-ins— such as Kutools. While Kutools is a paid add-in ($49 USD), I do think its tool-set is helpful. Similarly, Kutools can help you solve problems like this one. To conclude, I have covered how you can use functions, VB scripts, and Kutools for Excel to remove characters (like the “-“) from your Excel cells.
Thanks for taking the time to read our post. If you enjoyed the content or found our solution helpful, consider returning to the site for future Microsoft 365 problems. Each writer puts a considerable and commendable effort into constructing dependable resources for your benefit— we appreciate your support.