How to Remove Spaces in Excel With TRIM and SUBSTITUTE

To clean extra spaces in Excel, use TRIM; to delete every ordinary space, use SUBSTITUTE. With the original text in A2, enter =TRIM(A2) in B2 to remove spaces at the beginning and end and reduce repeated spaces between words to one. If you want to remove all ordinary spaces, enter =SUBSTITUTE(A2," ","") instead. These formulas create cleaned results in new cells, so keep the original data until you have checked the output.

What You’ll Need

A workbook in Microsoft Excel for Microsoft 365 on Windows. The same formulas work in Excel for Mac and Excel for the web.
A column or range containing the text you want to clean, such as A2:A100, and an empty column for the formula results.
Formulas below use commas between arguments, as in standard English-language Excel settings. In some regional settings, Excel expects semicolons instead; use the separator shown by your Excel installation.

Step-by-Step

Decide what kind of spaces to remove. TRIM is usually the right choice for names, labels, and sentences: it removes spaces before and after the text and changes multiple spaces between words to a single space. It does not join words together. SUBSTITUTE is the choice when every ordinary space must disappear, including spaces between words. For example, removing all spaces from a product code may be useful, but doing so to a sentence would make it difficult to read.
Check the source column and choose a destination. Suppose the text to clean is in A2:A100. Leave column A unchanged for now, and use an empty column such as B. Add a heading like Cleaned in B1 if that helps identify the results. Working in another column makes it easier to compare the original and cleaned text and to correct the formula if it removes too much.
Enter TRIM to clean ordinary extra spaces. With the source text in A2 and the result going in B2, enter =TRIM(A2) in B2. Press Enter. For example, if A2 contains two spaces before Maya, three between Maya and Chen, and two after Chen, B2 displays Maya Chen: one ordinary space between the words and none at either end. TRIM changes the result, not the contents of A2.
Use SUBSTITUTE when you want no spaces at all. With the same A2 source and B2 result layout, enter =SUBSTITUTE(A2," ","") in B2. The formula replaces each ordinary space character with an empty string. If A2 contains Maya Chen with one space, B2 displays MayaChen. This method removes spaces between words as well as at the edges, so confirm that joined text is the intended outcome.
Fill the formula down the rows. With the first cleaned result in B2 and the source data in A2:A100, select B2, then drag its fill handle (the small square at the cell’s lower-right corner) down to B100. Excel adjusts the references for each row, so B3 uses A3, B4 uses A4, and so on. Alternatively, select B2:B100 and choose Home > Fill > Down. In Windows, Ctrl+D fills down; in Excel for Mac, use Command+D. Excel for the web supports the ribbon command and fill handle, though keyboard shortcuts can depend on your browser.
Inspect several results before replacing anything. Compare the first, middle, and last rows, including entries with multiple words, blank cells, punctuation, and numbers stored as text. If one kind of entry needs spaces retained and another needs all spaces removed, use separate formula columns or apply different formulas to the relevant rows. A single formula cannot know which spaces are meaningful without an additional rule.
Make the results permanent only if you need to. Formula results update when their source cells change. If you want a fixed cleaned column, select the results and copy them, then select the destination cells and choose Home > Paste > Paste Values. Keep a backup or preserve the source column until you confirm the pasted values are correct. If the cleaned data is used by other formulas, check those references after replacing formulas with values.

For a more difficult case, consider a nonbreaking space. It can look just like an ordinary space but may remain after TRIM. With the source text in A2 and the cleaned result in B2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). This changes nonbreaking spaces to ordinary spaces, then trims the result. For instance, if A2 contains North, a nonbreaking space, and Shore, B2 displays North Shore with one regular space. This formula does not delete all ordinary spaces; use the SUBSTITUTE formula above if you want NorthShore instead.

Common Mistakes

Expecting TRIM to remove every space. TRIM preserves one space between words. Use SUBSTITUTE if the required result is a single continuous string.
Deleting spaces that separate words. SUBSTITUTE with a space as the search text removes every ordinary space. Check a few examples first, especially names, addresses, and descriptions.
Overwriting the source before checking. A formula in the same cell as its input would create a circular reference. Put the formula in another column, verify the output, and only then decide whether to paste values over the source.
Assuming every invisible gap is an ordinary space. Text copied from websites, PDFs, or reports can contain nonbreaking spaces or other characters. TRIM alone may not remove them. The CHAR(160) formula above handles one common cause.
Copying a formula with the wrong reference. A formula that refers to A2 should normally change to A3 when filled down one row. If it keeps referring to A2, check whether the reference was locked with dollar signs or whether you copied the cell in an unexpected way.
Confusing a blank result with a blank source. A cell containing only spaces may look empty, but a formula can return an empty-looking result. If the formula is supposed to preserve non-space characters, inspect the original cell before assuming the data is missing.

Tips

Use a small test area before applying a formula to thousands of rows. Include a normal entry, an entry with extra spaces, and an entry with a nonbreaking space if the data came from an external source.
Keep a clear distinction between cleaning spaces and changing text. TRIM retains word boundaries as one space; SUBSTITUTE removes them. Choose based on the way the cleaned values will be searched, displayed, or imported.
Formulas are not case-sensitive in this example, and they do not otherwise change letters, punctuation, or numbers. If you also need to remove line breaks or control characters, space removal alone may not be enough.
To remove only one specific occurrence of an ordinary space, Excel’s SUBSTITUTE function accepts an occurrence number. With the text in A2 and the output in B2, =SUBSTITUTE(A2," ","",1) removes the first ordinary space it finds and leaves later spaces unchanged. Use this only when the position of the space is meaningful.
For repeated work, keep a small note beside the cleaned column explaining whether the rule is “normalize extra spaces” or “remove all spaces.” That avoids confusion when another person reviews the workbook or updates the data later.

Troubleshooting

The formula appears in the cell instead of the cleaned text. The cell may be formatted as Text, or Excel may be showing formulas. Change the cell format to General, edit the cell, and press Enter. You can also check Formulas > Show Formulas; if that option is on, turn it off to display results. A leading apostrophe before the equals sign can also make Excel treat the entry as text, so remove it.

Excel reports a formula error. Check that the formula starts with an equals sign, uses straight double quotation marks, and has balanced parentheses. If your regional settings use semicolons as argument separators, replace the commas with semicolons. Also verify the referenced cell exists and contains the text you expect.

TRIM leaves a visible gap behind. The character may be a nonbreaking space rather than an ordinary space. Try the CHAR(160) version above. If that does not fix it, the text may include another invisible character, such as a line break. Compare the source with a manually retyped version, or identify the unwanted character before adding a replacement rule; blindly removing characters can damage valid text.

Some rows are clean but others are not. Make sure the formula was filled through the whole data range and that each row points to its matching source cell. Check for blank rows, merged cells, or formulas in the source column that return text with hidden characters. Sorting or filtering can also make a quick visual comparison misleading, so verify a few specific source and result pairs.

The result looks right but matching still fails. The mismatch may involve nonprinting characters, punctuation, or a number stored as text rather than spaces. First confirm the cleaned value in the formula result cell; then check whether the other value has the same character type and formatting. TRIM and SUBSTITUTE only address the characters they are designed to change.

Frequently Asked Questions

Does TRIM remove all spaces in Excel?
No. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. Use SUBSTITUTE when you need to remove every ordinary space, including those between words.

How do I remove spaces from an entire column?
Put the chosen formula beside the first row of data, then fill it down beside the rest of the column. Check the results before copying and using Paste Values. Avoid applying a formula over the source cells it refers to.

Can I remove spaces without changing the original data?
Yes. A formula in a separate column leaves the source cells intact. Keep both columns if you need the original data for reference, or copy the cleaned results elsewhere when you are ready to use them.

Why does TRIM not remove a space copied from a website?
Some websites and documents use a nonbreaking space, which looks like an ordinary space but is a different character. Replace CHAR(160) with a regular space and then apply TRIM, using the formula shown above.

Do these formulas work in Excel for Mac or Excel for the web?
Yes. TRIM, SUBSTITUTE, and CHAR work in current Excel for Windows, Mac, and the web. The formula syntax is the same, although regional argument separators may differ. Filling down and pasting values are available in all three, with small differences in shortcut behavior.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.