How to Remove Spaces from an Excel String Cell

 Easy Formulas to Remove Spaces

Excel formulas to remove spaces and clean your data efficiently.

Simply, the paces inside an Excel string Cell are either:

• leading/starting spaces
• Mid spaces
• Trailing/ending spaces

Plus, if you are exporting the data from the internet or a database, you may have some problematic characters (unprintable or 7-bit ASCI characters). We will handle this too.

✋ Before we start, we will assume that the data is in cell A1. We will insert the formulas starting from B1.Feel free to change cells reference to your references.

How to remove all extra spaces from an Excel cell?

This will include leading, mid, and trailing spaces

=TRIM(A1)

or

=SUBSTITUTE(A1," ","")

Remove Spaces and Clean Unprintable Characters

=TRIM(CLEAN(A1))

You may still see characters like(� , ⌷ , ¶) which are called "unprintable characters". How to remove them? 

 Most famous codes for the unprintable characters are:

• char(160) → �
• char(127) → ⌷
• char(182) → ¶

To remove these characters, you add them inside a SUBSTITUTE function (or a nested SUBSTITUTE function).
We will assume that we both � and ⌷ inside our string cell. So, we must have 2 SUBSTITUTE functions nested for each. Remember: � is char(160), ⌷ is char(127).

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),CHAR(127),"")))

And a bonus:

How to delete blank rows with keyboard shortcuts:

Step-by-step guide to delete blank rows and clean your Excel dataset.

1. Click on top of the column to highlight it all. or, inside any cell of the column Ctrl+Spacebar
2. Press F5
3. Click on Special
4. Click on Blanks
5. Press on Ctrl+-
6. Choose "Entire row" and press ok or Enter

Why Removing Extra Spaces Matters in Excel

Extra spaces in Excel cells are one of the most common reasons formulas fail unexpectedly. A VLOOKUP that returns an error, a SUMIF that ignores rows you expect it to count, or a pivot table that shows duplicate categories are almost always caused by invisible leading, trailing, or extra spaces hidden inside text values. When data comes from web forms, databases, or CSV imports, these stray spaces are extremely common and difficult to spot with the naked eye.

The TRIM function is the first line of defense. It strips leading and trailing spaces and reduces any sequence of internal spaces down to a single space. However, TRIM alone cannot handle non-breaking spaces or other unprintable characters. That is where combining TRIM with CLEAN and SUBSTITUTE becomes essential. The CLEAN function removes the first 32 non-printable characters in the ASCII table, while SUBSTITUTE lets you target specific characters such as CHAR(160) (non-breaking space) that TRIM does not recognize.

A practical workflow is to put your cleaning formula in a helper column, verify the results, and then copy-paste the column as values over the original data. This way your source data stays intact while you confirm the cleaning worked correctly before committing to the change.

Key Takeaways

• Use =TRIM(A1) to remove leading, trailing, and extra interior spaces in one step.
• Use =SUBSTITUTE(A1," ","") when you need to remove every single space, including those between words.
• Combine TRIM with CLEAN to also strip unprintable ASCII characters from imported data.
• Nest SUBSTITUTE calls inside TRIM(CLEAN(...)) to target stubborn characters like CHAR(160) and CHAR(127).
• Always place the cleaning formula in a helper column first so you can review results before overwriting original data.
• To delete blank rows quickly, select the column, press F5, choose Special, click Blanks, then press Ctrl+- and choose Entire row.

Frequently Asked Questions

What is the difference between TRIM and SUBSTITUTE for removing spaces?

TRIM removes leading and trailing spaces and collapses multiple internal spaces to one, while SUBSTITUTE(A1," ","") removes every space character entirely, including those between words. Use TRIM when you want readable text with normal spacing, and SUBSTITUTE when you need a space-free string.

Why does TRIM not remove the non-breaking space character?

The non-breaking space (CHAR(160)) is a different character from the regular ASCII space. TRIM is designed to handle only the standard space (ASCII 32), so you need SUBSTITUTE(A1,CHAR(160),"") to target it explicitly.

Can I combine TRIM, CLEAN, and SUBSTITUTE in a single formula?

Yes. A robust cleaning formula is =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),""))). This first removes the non-breaking space, then strips unprintable characters, and finally trims any remaining leading, trailing, or excess spaces.

How do I clean an entire column at once?

Place the cleaning formula in the cell next to the first row of data, then drag or double-click the fill handle to apply it to the entire column. After verifying the results, copy the helper column and use Paste Special > Values to replace the original data.