Extra spaces in Excel can be invisible but still break lookups, IF comparisons, joins, and duplicate checks. TRIM is the usual cleanup tool because it removes spaces at the beginning and end of text and reduces repeated internal spaces to one.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when copied text from websites, PDFs, emails, exports, or forms does not match correctly.
Remove extra spaces with TRIM
Use a helper column so you can check the cleaned result before replacing data.
- Click a blank cell beside the messy text.
- Type =TRIM(A2), changing A2 to your first source cell.
- Press Enter.
- Fill the formula down the column.
- Select the cleaned helper column and copy it.
- Right-click the original column and use Paste Special > Values.
- Delete the helper column after confirming the results.
TRIM keeps one normal space between words. That is usually what you want for names, addresses, titles, and sentences.
Remove every space
Use SUBSTITUTE only when spaces should not exist at all, such as in codes or phone-style strings.
- In a helper cell, enter =SUBSTITUTE(A2,” “,””).
- Fill the formula down.
- Copy the results.
- Paste Special > Values over the original cells or into a final output column.
More control
Fix non-breaking spaces
Text copied from the web may contain non-breaking spaces that look normal but are not removed by TRIM. Use SUBSTITUTE with CHAR(160), then TRIM the result, such as =TRIM(SUBSTITUTE(A2,CHAR(160),” “)).
Use Flash Fill for a short list
Type the corrected version of the first one or two cells beside the messy data, then press Ctrl+E or use Data > Flash Fill. Excel fills the pattern for the remaining rows.
Check with LEN
Compare LEN before and after cleanup. If the length does not change, the problem may be a non-breaking space or another hidden character. CLEAN can help remove some non-printing characters.
Sources
- Microsoft Support – TRIM function (2025)
- Microsoft Support – SUBSTITUTE function (2025)
- Microsoft Support – Flash Fill in Excel (2025)