How To Remove Extra Spaces In Excel (5 Super Easy Ways)

You know what kind of data looks like a big piece of work the moment you see it; everything is so haywire and all over the place. Spaces are where they shouldn't be, line breaks are stretching text all the way down, some symbols thrown in for good confusing measure. Yes, you have your work cut out for you but we'll try to make it as breezy as possible.

Such Excel pandemonium is the result of badly copied data (or badly pasted, more like). If data is split into lines from where it is being copied, it might be pasted with many space characters or line breaks and sometimes we don't just carry space characters, we may have introduced our sheets to other characters. Those characters and spaces that wouldn't go no matter how much you've tried to maneuver them away without having to handpick them? Don't worry, we're not asking you to handpick them either.

This tutorial is about getting your data in the right shape in Excel. Some data anomalies can be fixed with Flash Fill but as other wholesome solutions, we'll teach you how to remove extra spaces, line breaks, and nonprinting characters from cells. But first, let's find out what these are and why are they a menace for our worksheets.

What Are We Dealing With?

• Extra Spaces – Extra spaces are the spaces which we don't require in our data. They can be several in number and also have types (mentioned ahead).
• Leading Spaces – are spaces at the start of the text in a cell.
• Trailing Spaces – are the spaces at the end of the text in a cell.
• Spaces in-between Text – are the extra spaces in word spacing. You may only need a single space or no space at all.
• Line Breaks – are new lines (manually entered with Alt + Enter keys).
• Nonprinting Characters – are characters displayed for content designing but are not displayed in prints.

Why are these problems?

While data mining or analyzing, it would be hard to trace text out because we'd be looking for "A" but couldn't find it because the text in the cell is "space characterA". This is one of many problems. So let's save the sheet aesthetics and analysis distress and learn how to trim away the unwanted characters.

Method #1 – Use TRIM Function

The TRIM function removes all spaces from a text string except for single spaces between words. The TRIM function will remove all leading and trailing spaces and extra word spacing. This implies that if there are accidental extra spaces breaking a word, the function will eliminate the extra spaces but will treat one space as required. If you aim to remove all space characters from a cell, head to the last couple segments of this guide.

Now let's watch the TRIM function removing leading and trailing spaces and extra word spacing. Here's the formula:

The function only needs one argument, the reference to the cell with the text, to do its work. Below, we will use the TRIM function to eliminate all needless space characters from the list of names:

The TRIM function has cleaned up all unrequired spaces from the data above. If there were any accidental spaces splitting a name e.g. "John" was split to "Jo   hn" with 3 space characters in between, the TRIM function would shorten it to "Jo hn" with a single space character. The function removes ALL leading and trailing spaces and removes all extra spaces between text & saves one space.

Method #2 – Use CLEAN and TRIM Functions

As seen from above, the TRIM function removes all extra spaces in a cell. The CLEAN function removes all nonprinting characters from text. Together, we'll use the CLEAN and TRIM functions to remove extra spaces, line breaks, and other nonprinting characters.

The combined functions will look like this:

The TRIM and CLEAN functions have been paired together to work on one argument; cell B3. The CLEAN function clears B3 of all the line breaks and nonprinting characters, leaving all the extra spaces to be passed onto the TRIM function for clear-up. The result is a tidied-up cell of text with the right spaces.

You may think the formula would still work with the functions switched the other way round but we suggest you stick with this order. Using the TRIM function first would leave the line breaks and nonprinting characters. Using the CLEAN function later would rid the cell of both of these but may sometimes leave a space behind; contributing to needless extra spaces in the results. Hence, we recommend the above formula for consistent results.

Method #3 – Use CLEAN, TRIM, & SUBSTITUTE Functions

A quick recap; the TRIM function is for the extra spaces and the CLEAN function is for nonprinting spaces. The SUBSTITUTE function replaces existing text with new text in a text string. The SUBSTITUTE function here is being used for two things; to show you how to use SUBSTITUTE to remove nonbreaking spaces (a type of nonprinting character) from text and to remove the single spaces leftover from the TRIM function.

We'll show you an all-exclusive formula below to eliminate the following characters:

• extra spaces,
• line breaks,
• nonprinting characters.

You can pick out your target functions from the formula and apply them to your scenario. This is the formula we have pieced together:

Before we get a grip on this formula, what is CHAR(160)? CHAR(160) is a nonbreaking space and we have set the SUBSTITUTE function to replace all CHAR(160) spaces with a simple space character which is enclosed in double quotes " ". If this still doesn't remove the stubborn spaces, you may have to experiment with CHAR codes to see exactly which character you are dealing with and need to eliminate.

When the SUBSTITUTE function has removed all the CHAR(160) spaces from B3, the CLEAN function clears all the line breaks and nonprinting characters. Passed onto the TRIM function, TRIM removes all the extra space characters, leaving a single space between the names.

Now this formula was for working with text where you would need a single space between words (e.g. names, addresses). This is the prime job of the TRIM function, so if you want to remove all spaces, the formula would change a bit. Of course the first thing to go would be the TRIM function. The rest of the details are below.

Method #4 – Use CLEAN and SUBSTITUTE Functions

Sometimes it may be necessary to remove all unwanted spaces and special characters to retrieve the text in one solid chunk (e.g. phone numbers). We will use a formula similar to the one above but without the TRIM function. With the TRIM function gone, we need its replacement with a function that will take care of all the space characters and that's where the SUBSTITUTE function extends its help. The formula we have coined to remove all the space characters, line breaks, and nonprinting characters is:

The SUBSTITUTE function has made two appearances here; one for removing all regular space characters and one for removing a certain nonprinting character: CHAR(160) which is a nonbreaking space. If you're still encountering persistent spaces after using this formula, you may need to find which CHAR code is causing trouble and adjust it in the formula.

The other SUBSTITUTE function will replace all space characters with empty text. This formula

can be used on its own to remove all regular space characters from a cell.

The CLEAN function then removes all nonprinting characters from the result of the SUBSTITUTE functions, delivering one string of numbers with all the spaces and characters removed.

Method #5 – Use Find and Replace Option

The Find and Replace feature can be used to remove all the spaces from a cell. We can also use Find and Replace to remove line breaks but each replacement has to be done separately. This is how to remove all spaces and line breaks from a cell:

• Select the cells from where you want to remove the spaces.
• In the Home tab's Editing group, click on the Find & Select button and select Replace… from the list. This will open the Find and Replace dialogue box. You can also use the keyboard shortcut Ctrl + H for this action.

• In the Find what field, enter the character you want to replace (e.g. space character, hyphen, comma). We have entered a single space character. Leave the Replace with field blank so the space character can be replaced with empty text.

• Click on Replace All and then click on OK in the confirmation dialog that will pop up. Now that all of the spaces have been replaced, we'll eradicate the line breaks too.
• Clear the Find what field in the Find and Replace dialog box (which will still be open after making the first replacement) and press Ctrl + J. You will only see a tiny blinking dot but this will replace line breaks.
• Leave the Replace with field blank again.

• Click Replace All, hit OK in the confirmation pop-up, close the Find and Replace dialog box if done.

If your data hasn't been text wrapped already, you may need to adjust the row heights leftover from removing line breaks.

There's the cleaned-up data ready to be used:

Time's up! That was all on how to remove extra spaces in Excel. We hope to have solved some stubborn space issues for you so your data sweeping is not a manual nightmare. We're up for solving more manual nightmares for smooth Excel daydreaming. See you with solutions!