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