To remove every ordinary space from a column in Excel, select the column or range, press Ctrl+H, enter one space in Find what, leave Replace with empty, and choose Replace All. If you need to keep one space between words while removing extra spaces at the beginning, end, or between words, use Excel’s TRIM function instead. Make a copy of the original data first so you can check the results before replacing it.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The steps also work in recent desktop versions of Excel.
- A workbook containing the column you want to clean. If the data matters, save a copy or duplicate the worksheet before making changes.
- An empty helper column if you want to clean the values with a formula and review the output before replacing the original data.
Step-by-Step
- Decide what “remove spaces” means for your data. Excel can remove every ordinary space, including spaces between words, or it can tidy extra spaces while preserving normal word spacing. For example, removing every space changes
North HarbortoNorthHarbor. Trimming changesNorth HarbortoNorth Harbor. Choose the result you actually need before editing: deleting spaces from product codes or phone numbers is different from cleaning names and addresses. - Protect the original values. Save the workbook, or copy the source column to a new worksheet or an unused column. To copy a column, select its letter heading, press Ctrl+C on Windows, click an empty destination, and press Ctrl+V. On a Mac, use Command+C and Command+V. If the column contains formulas, check whether you want to preserve formulas or their displayed results; copying and pasting normally can carry formulas into the copy.
- Use Find and Replace when you want to delete every ordinary space. Select only the cells you want to change. Selecting the column letter selects the whole column; selecting a specific range limits the edit to those cells. Open Home > Find & Select > Replace, or press Ctrl+H on Windows. In the Find what box, press the spacebar once. Leave Replace with completely empty, then select Replace All. Excel removes ordinary spaces from the selected cells, including spaces between words. If you did not select a range first, review the dialog’s search scope and avoid replacing spaces throughout the entire worksheet by mistake. On Mac, use the Find and Replace command from the Home tab or Edit menu; Control+H is the common Replace shortcut.
- Check the Replace All result before continuing. Excel reports how many replacements it made. Look at a few affected cells, including one with multiple words and one with a code or number. If words have run together when you wanted them separated, immediately press Ctrl+Z on Windows or Command+Z on Mac, then use the formula method in the next step. Undo is useful for a recent replacement, but it is not a substitute for keeping a backup.
- Use TRIM to remove extra spaces but keep word separators. This is usually the better choice for names, addresses, descriptions, and imported text. Assume the original text is in A2:A100 and column B is empty. In B2, enter
=TRIM(A2), then press Enter. If A2 contains two spaces before “Mina,” three between “Mina” and “Chen,” and one after “Chen,” the result isMina Chen. TRIM removes spaces at the start and end and reduces repeated ordinary spaces between words to one. - Fill the formula down the column. Select B2, then double-click the small square at the lower-right corner of the cell, called the fill handle. Excel fills the formula down alongside the neighboring data. Alternatively, drag the fill handle to the last row, or select the destination range and press Ctrl+D on Windows. On Mac, use the fill handle or the Fill Down command. Check the last populated rows so the cleaned results cover the full source list.
- Replace the original values only after checking the helper column. Compare several results with their source cells and look for missing words, unexpected changes, or blanks. If the output is correct, copy the cleaned cells in column B. Select the original cells in column A, then use Home > Paste > Paste Values (the values-only paste option) so the formulas do not replace the source with references. In many Windows versions, the Paste Special values option is also available from the right-click menu. Keep the backup until you are sure the workbook still behaves as expected.
- Use a formula to remove every ordinary space when that is intentional. This approach lets you review the result before overwriting anything. Assume the source values are in A2:A100 and B is empty. In B2, enter
=SUBSTITUTE(A2," ",""). WithAB 123in A2, the result isAB123. Fill the formula down, check the output, and paste values over the original only if that is the required format. The formula removes ordinary spaces only; it does not automatically remove every kind of invisible character. - Clean nonbreaking spaces if TRIM does not change the text. Some web pages and exported files contain nonbreaking spaces that look like ordinary spaces but are not treated as such by TRIM. Assume the original text is in A2:A100 and B is empty. In B2, enter
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). If A2 containsMina Chen, where the gap includes nonbreaking spaces, the result should beMina Chen. Fill down and review the output before pasting values. This converts character 160 to an ordinary space, then TRIM tidies the spacing. - Save and verify the finished column. Save the workbook after confirming that the cleaned values are correct. Check a few records near the top, middle, and bottom, and use a representative value in any formulas, lookups, or imports that depend on the column. If the column is used by other worksheets, confirm that those references still point to the intended cells.
Common Mistakes
Removing spaces that separate words. Find and Replace with an empty replacement deletes every ordinary space, not just unwanted ones at the edges. It can turn a customer name, address, or description into a single string. Use TRIM for normal text cleanup, and reserve full deletion for data such as identifiers where spaces are not meaningful.
Assuming TRIM removes every kind of whitespace. TRIM handles ordinary spaces (character 32), but it does not remove every invisible character. Nonbreaking spaces from websites and some exported systems can remain. If the formula appears to make no difference, try the CHAR(160) method above rather than repeatedly applying TRIM.
Cleaning a whole worksheet unintentionally. If you use Replace All without selecting a range, Excel may search more cells than you meant to change. Select the intended range first and check the replacement scope in the dialog. Also remember that Replace All can affect formulas or other cell contents, depending on the search settings and workbook.
Leaving formulas in the final data column. A helper formula displays cleaned text but still depends on the source cell. If you delete or move the source, the result can change or become an error. Copy the reviewed output and paste values if you need fixed, independent text.
Overwriting useful formatting or data types. Pasting cleaned values can affect formats if you use a regular paste. Choose Paste Values when you want the text result without copying the helper column’s formatting. If codes include leading zeroes, confirm that Excel has kept them as text; cleaning spaces does not restore zeroes already removed by number formatting.
Tips
For a one-time edit, Find and Replace is quick. For a large or recurring import, a helper formula is safer because you can compare the source and result before committing the change. Keep the source column until the cleaned data has passed your checks.
Use a small test range first when the column contains mixed content, such as names, postal codes, and notes. Examine values with spaces at the beginning, spaces at the end, multiple spaces between words, and no spaces. This reveals whether your chosen method matches the whole dataset rather than just one example.
For text that has unwanted line breaks as well as spaces, TRIM alone may not be enough. CLEAN can remove many nonprinting control characters, but it does not remove every Unicode character. Assume the source text is in A2 and the cleaned result will go in B2. Enter =TRIM(CLEAN(A2)). For ordinary text containing a line break and extra spaces, this can leave a tidier result such as Unit 4 West. Check the output carefully: CLEAN may remove control characters that are meaningful in a particular dataset, and it does not replace the dedicated nonbreaking-space cleanup when character 160 is present.
If you need a permanent workflow, label the helper column clearly, such as “Cleaned name,” and keep it next to the untouched source during review. That makes it easier for a colleague to understand what changed and gives you a simple way to compare the before-and-after values.
Troubleshooting
Replace All says it made zero replacements. Confirm that the Find what box contains an actual space. If it does, check whether the cells use nonbreaking spaces or another invisible character. Try the formula with CHAR(160), and make sure you selected the cells containing the text rather than an unrelated range.
TRIM seems to do nothing. TRIM only changes leading, trailing, or repeated ordinary spaces. A cell with exactly one ordinary space between each word is already in the format TRIM produces. If visible gaps remain unusually wide or the text will not match in a lookup, test for nonbreaking spaces with the SUBSTITUTE and CHAR(160) formula.
The formula appears as text instead of returning a result. Check that the cell is not formatted as Text. Change its format to General, edit the cell (press F2, then Enter on Windows; on Mac, edit the cell and press Return), and verify that the formula begins with an equals sign. Also check that Show Formulas is not turned on; press Ctrl+` on Windows to toggle it.
The cleaned result has words joined together. You likely used Find and Replace or SUBSTITUTE to delete every space. Undo if possible, or return to the original data and use TRIM instead. If the source was already overwritten and there is no backup, try Undo immediately; otherwise recover an earlier workbook version if one is available.
Some rows were not filled or became blank. The fill handle may have stopped at a gap in a neighboring column, or the source range may contain empty cells. Fill the formula to the exact last row of the data, then compare row numbers. A blank result may also be correct if its corresponding source cell is blank.
Frequently Asked Questions
How do I remove spaces from an entire Excel column?
Select the column, open Home > Find & Select > Replace, enter one space in Find what, leave Replace with empty, and select Replace All. This deletes spaces between words as well as at the edges, so use it only when that is intended.
How do I remove extra spaces but keep one space between words?
Use TRIM in a helper column. For example, with source text in A2, =TRIM(A2) removes leading and trailing ordinary spaces and reduces repeated spaces between words to one.
Can I remove spaces without using a formula?
Yes. Find and Replace can delete every ordinary space from a selected range. It cannot selectively keep one separator between words while removing only extra spaces, so use TRIM for that kind of cleanup.
Why does Excel still show spaces after I use TRIM?
The text may contain nonbreaking spaces, line breaks, or other nonprinting characters. Try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for nonbreaking spaces. If the issue is a line break or another control character, test CLEAN and inspect the result.
Will removing spaces change numbers or dates?
It can change how Excel interprets text values, codes, or imported data, even if the displayed result looks similar. Test a copy first, especially for values with leading zeroes, dates, or identifiers. If spaces are part of a required code format, do not remove them.
Can I undo a space removal?
Press Ctrl+Z on Windows or Command+Z on Mac soon after the edit. Undo may not be available after closing the workbook or making many later changes. A saved copy or untouched source column is the more reliable way to restore the original data.
Related
- Excel Conditional Formatting Question Mark Explained
- Excel Conditional Formatting Greyed Out? Fixes to Try
- How to Add a Dropdown List in Excel for Data Entry
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets IFERROR: Show a Blank Instead of ErrorsYes. In Google Sheets, wrap a formula in IFERROR and use an empty string as its fallback: =IFERROR(your_formula,…
- Inventory Spreadsheet: Simple Setup and Useful TipsYes—an inventory spreadsheet can be simple: list each item once, record its quantity and reorder point, and update…
- Excel Power Query: Import Multiple CSV FilesTo import and combine multiple CSV files in Excel for Microsoft 365 on Windows, put the files in…
- Excel Slicers: A Clear Guide to Filtering DataIn Excel for Microsoft 365 on Windows, a slicer is a set of clickable buttons that filters a…
- Add a Watermark in Word on One Page: Step-by-Step GuideTo add a watermark to just one page in Word, isolate that page in its own section, unlink…
- Outlook Calendar: How to Find and Remove DuplicatesYes—duplicate Outlook calendar events can be removed by deleting the extra entries, but Outlook does not offer one…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.