We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Whether it’s the result of getting data from online or typing errors, there are many reasons why you may need to clean up extra spaces in Excel.
These extra spaces can make your sheet look messy and keep you from correctly using some of Excel’s tools and features.
3 Ways to Remove Extra Spaces from an Excel Sheet
Watch: Ways to Clean up Extra Spaces in Excel – Tutorial
1. Get Rid of Extra Spaces Using the TRIM Function
The TRIM function removes any spaces from the beginning and end of a cell in Excel.
Let’s say you have a list of email addresses with extra spaces both before and after the address.
In a new column, apply the TRIM function, referencing the cells with the emails you want to clean up.
Your formula would look something like this:
=TRIM(A1)
The results of your TRIM function will have the extra spaces removed.
You can then either save your cleaned results as a new file or copy them and paste them as values over the original ones.
To save time, you can even apply TRIM to a range of cells at one time.
2. Clean up Extra Spaces and Line Breaks Using TRIM + CLEAN Together
Cells are a little trickier to clean up when they contain extra spaces and line breaks – or other non-visible formatting.
If your cells contain line breaks, you’ll need to nest the CLEAN function with the TRIM function to clean them.
Your nested TRIM + CLEAN formula would look like this:
=TRIM(CLEAN(A1))
Then, like in the first example, you can either save your cleaned results as a new file or copy them and paste them as values over the original ones.
What does the CLEAN function do???
CLEAN removes 32 different non-printable characters from Excel cells.
More specifically, the first 32 ASCII characters – but that gets a bit technical.
The line break, or paragraph break, is probably the most common one you’ll find in your Excel sheets.
3. Use Find & Replace to Remove Extra Spaces Between Words
What if you need to remove extra spaces from between words or other content?
This can require a few more steps and a bit more caution.
A common example of this is the use of spaces after a period.
It’s now the norm to type only 1 space after the final punctuation, but a lot of people were taught, and got used, to typing 2 spaces at the end of each sentence.
You can use Find & Replace (keyboard shortcut Ctrl+H) to remove these extra spaces.
NOTE: It’s important to be cautious with this. You’ll want to avoid deleting extra spaces that are there intentionally.
To use this, you could type 2 spaces in the “Find What” field and 1 space in the “Replace With” field.
If you want to be extra cautious, you could add the specific punctuation before the spaces.
For example, you could use a period followed by 2 spaces in the “Find What” field and a period followed by 1 space in the “Replace With” field.
Then repeat this as needed for additional punctuation until your cells are cleaned to your liking.
FYI, this was created using the desktop version of Excel in Microsoft 365.
Leave a Reply