To remove ordinary spaces and hyphens from a cell in Excel, enter =SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","") in a blank column, replacing A2 with the cell you want to clean. Fill the formula down, then copy the results and use Paste Special > Values if you need to replace the original entries. For a one-time cleanup, use Find and Replace instead. Make a copy of your data first, especially if the values are important or difficult to recreate.
What You’ll Need
- An Excel workbook containing the text you want to clean. The steps below are for Microsoft Excel for Microsoft 365 on Windows; the formula also works in Excel for Mac and Excel for the web.
- A spare column if you plan to clean the data with a formula. Leave the original column in place until you have checked the results.
- A clear idea of which characters you want removed. A regular space and a hyphen-minus (-) are different from an en dash (–), em dash (—), or nonbreaking space.
- A backup copy of the workbook, or at least the original values in a separate column, before using Replace All on your source data.
Step-by-Step
- Identify the data and the characters to remove. Check a few representative cells and decide whether you want to remove every ordinary space and hyphen, or only separators in a particular field. For example, a phone number such as 555-0100 can become 5550100, and a product code such as AB 12-34 can become AB1234. Removing separators changes the text, so consider whether spaces or dashes carry meaning in your data. If you want to remove only leading and trailing spaces, or collapse repeated spaces to one, use a different approach; the formula and Replace All method here remove every matching character.
- Choose a method. Use a formula when you want to keep the original data visible, check the cleaned result, or repeat the same cleanup on many rows. Use Find and Replace when you want to edit the selected cells directly and are confident the characters should be removed everywhere in that selection. If the workbook is valuable, save a separate copy before editing. Find and Replace changes cells in place, and those changes may be difficult to review after you save and close the file.
- Set up a formula in a new column. If your source values are in A2:A100, click B2 and enter
=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""). The inner SUBSTITUTE removes ordinary spaces; the outer SUBSTITUTE removes hyphens. For example, if A2 contains 555-0100, the result in B2 is 5550100. If A2 contains AB 12-34, the result is AB1234. Adjust A2 to match the location of your first source value, and make sure the formula is in a different column so it does not overwrite the source. - Fill the formula down the column. Select the formula cell and drag the small square at its lower-right corner down to the last row, or double-click that square if the neighboring data is continuous. You can also copy the formula, select the remaining destination cells, and press Ctrl+V on Windows. Excel updates the row reference automatically, so the next row uses A3, then A4, and so on. Check the first, middle, and last few results to confirm the formula reached the intended rows.
- Check for other kinds of spaces or dashes. The basic formula removes the ordinary space character and the standard hyphen-minus only. Text copied from websites or other systems may contain a nonbreaking space, which can look like an ordinary space but remain after cleaning. For data in A2 that may contain ordinary spaces, hyphens, en dashes, em dashes, and nonbreaking spaces, use this version in a separate result cell:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"–",""),"—",""),CHAR(160),""). For example, it removes those characters from one cell while retaining its letters and digits. Use it only if you genuinely want all of those separator types removed. - Review the output before replacing anything. Compare several cleaned cells with their originals. Look for missing digits, unexpected empty results, or characters you meant to keep. If a value is an identifier, check its exact characters rather than relying only on how Excel displays it. The formula produces a new result and leaves the source untouched, so you can revise the formula or start again without retyping the original data.
- Turn formula results into fixed values if needed. If the cleaned column should no longer depend on the source, select the results and copy them. Right-click the destination cells and choose Paste Special > Values, or use Home > Paste > Values. You can paste over the original column only after confirming the cleaned values are correct. Keep the original column until you have verified the pasted results and saved the workbook.
- Or use Find and Replace for a direct cleanup. Select only the cells you want to change. On Windows, press Ctrl+H, or go to Home > Find & Select > Replace. In Find what, enter one ordinary space; leave Replace with empty; then select Replace All. Next, enter a hyphen-minus (-) in Find what, leave Replace with empty again, and select Replace All. Excel performs the two replacements separately. Read the reported replacement count after each pass. In Excel for Mac, use Home > Find & Select > Replace; ribbon wording or keyboard shortcuts can vary by release. Excel for the web has a similar replace control, though its dialog layout may differ.
- Confirm the final data. After using Replace All, inspect the selected cells and a few values elsewhere in the workbook if you worked on a whole column. If the replacement affected the wrong text, use Ctrl+Z on Windows immediately, or Command+Z on Mac, provided you have not taken an action that prevents undo. For a formula-based cleanup, keep the original column until the cleaned values have been checked and saved.
Common Mistakes
Replacing in the entire worksheet by accident. If no range is selected, Find and Replace can search beyond the cells you intended to clean. Select the target range first and confirm the scope in the dialog before choosing Replace All. A small test selection is useful when you are unsure how the workbook is structured.
Assuming every dash is the same character. A hyphen-minus, en dash, and em dash can look similar, but replacing one does not automatically remove the others. This is common with pasted text, formatted phone numbers, and ranges written with a longer dash. Test the formula or Find and Replace on a sample cell and check the exact character that remains.
Deleting characters that are meaningful. Removing spaces from full names, addresses, or sentences can make them unreadable. Removing a dash from a code can also change how it is interpreted by another system. Clean only the fields where the new format is wanted, and retain an original column when the source format may be needed later.
Overwriting the source too early. A formula can be filled down incorrectly, or a replacement can remove more than expected. Do not delete the original data or paste over it until you have reviewed the output. If you need to overwrite, copy the workbook first or preserve a separate source column.
Losing leading zeros in identifiers. Excel may store a value such as 00123 as the number 123 and show the leading zeros only through cell formatting. A cleanup formula may not preserve formatting-based zeros. If the exact identifier matters, store or import it as text and verify the cleaned result against the original identifier, not just its display.
Tips
Use a formula when you want a reversible, auditable process. It is especially helpful if new rows arrive regularly: you can copy the formula down for each batch and compare the source and cleaned columns side by side. After you confirm the output, Paste Special > Values creates fixed text results. If you need a live result that updates whenever the source changes, keep the formulas instead.
To limit changes, select just the relevant cells before using Find and Replace. This is safer than selecting a full column when that column also contains headings, notes, or unrelated text. For a small number of cells, you can select a range with the mouse; for a continuous column of data, click its first data cell and use Ctrl+Shift+Down on Windows to extend the selection through the contiguous block. Check the highlighted range before replacing.
If you need to keep spaces between words but remove only spaces at the beginning or end, do not use the all-spaces formula. Excel’s TRIM function is designed to remove leading and trailing ordinary spaces and reduce repeated internal ordinary spaces to single spaces, which is a different result. It does not remove hyphens. Choose the operation that matches the data you need, rather than applying a broad cleanup automatically.
For a reusable workflow, keep a short note beside the cleaned data describing which separators were removed. This is useful when another person needs to interpret the column or compare it with a source file. If the data feeds an import, matching process, or external system, confirm the expected format before changing separators; some systems treat punctuation as part of the identifier.
Troubleshooting
The formula displays as text instead of calculating. Check whether the cell is formatted as Text. Change it to General, then re-enter the formula by pressing F2 and Enter, or delete and type it again. Also make sure the entry begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles formula display; the key may be in a different position on some keyboards.
Some spaces remain after using the formula. The characters may be nonbreaking spaces or another whitespace character rather than an ordinary space. Try the extended formula in Step 5, which also removes character 160 and common dash variants. If characters still remain, copy one affected value into a test cell and inspect it carefully; the source may contain a different Unicode character that needs its own replacement.
Replace All reports zero replacements. Confirm that the correct cells are selected and that Find what contains the exact character. For a space, the field must contain one space, not be empty. For a dash, copy the character from a problem cell if you are unsure which dash it uses. In the Find and Replace dialog, check that no unintended formatting criteria are set; clear them if needed, then try a small sample range.
The result has lost leading zeros or other formatting. Check whether the source is a number with a custom number format, rather than text containing those zeros. The displayed appearance can differ from the stored value. If the value is an identifier, keep it as text where possible and verify the required length and digits. Do not assume that changing the cell’s number format will restore characters that were not stored in the underlying value.
Undo does not restore the old data. Undo may no longer be available after closing the workbook or performing certain actions. If the original values were overwritten, retrieve them from the backup, version history, or original source file. This is why a separate formula column or saved copy is a safer choice for large or important cleanups.
Frequently Asked Questions
Can I remove spaces and dashes at the same time?
Yes. The nested SUBSTITUTE formula removes both ordinary spaces and hyphens in one calculation. Find and Replace also works, but you must run Replace All once for spaces and again for hyphens.
Will this remove spaces at the start and end only?
No. The formula and replacement steps remove every matching ordinary space in the selected text, including spaces between words. If you want to tidy leading, trailing, and repeated spaces while keeping single spaces between words, use TRIM instead; it does not remove hyphens.
Does the formula remove en dashes and em dashes?
The basic formula removes only the standard hyphen-minus. Use the extended formula if you also want en dashes and em dashes removed. You can also replace each dash character separately with Find and Replace.
Can I remove spaces and dashes without a formula?
Yes. Select the target cells, open Home > Find & Select > Replace, and replace a space with nothing. Repeat with a hyphen. This edits the selected cells directly, so save a copy first if you may need the original values.
Will the formula update if I change the original cell?
Yes, as long as the result cell still contains the formula. It recalculates from the referenced source cell. If you paste the results as values, they become fixed and will not update when the source changes.
Does this work in Excel for Mac or the web?
The SUBSTITUTE formula works in Microsoft 365 for Mac and Excel for the web. Find and Replace is also available, although the location or appearance of its controls can differ slightly from Windows. Use the Home tab’s Find and Replace controls if a keyboard shortcut does not match your version.
Related
- Insert a Yes/No Dropdown in Excel: A Step-by-Step Guide
- How to Insert a Row in Excel on a Laptop, Step by Step
- Insert Rows in Excel Multiple Times: A Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Undo Block Sender in Outlook and Unblock EmailsTo undo Block Sender in Outlook, remove the address or domain from your blocked senders list: in Outlook…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, select Add…
- How to Add Footnotes in Word at the Bottom of a PageTo add a footnote at the bottom of a page in Microsoft Word, place the cursor where you…
- Block a Sender in Outlook Mail: Steps, Limits and TipsTo block a sender in Outlook, select a message from that person, open the message actions or Junk…
- Security Risk in Excel: How to Find and Check It SafelyTo find security risks in an Excel workbook, first check where it came from and whether Excel shows…
- Excel: Count How Many Times a Value Appears in a RangeTo find how many times a value appears in an Excel range, use =COUNTIF(A2:A10,D2), where A2:A10 contains the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.