Essential Steps To Remove Spaces in Excel That You Need To Know

Did you know there are easy ways to remove blank spaces in Excel?

Pay attention to extra spaces because they may cause issues with your formulas. Two cells will be seen as different, even if they have the same words. Even a single-space character can cause such a discrepancy.

In this blog post, we'll show you the method that will work best for your specific task and data type.

Read on as we cover the following:

• Remove Spaces with the TRIM function

• Remove Line Breaks and Non-Printing Characters

• Remove Non-Breaking Spaces

• Remove All Spaces

• Remove Spaces with Find and Replace

• Remove Spaces Using the Substitute Function

• Final Thoughts on Remove Spaces in Excel

• Frequently Asked Questions on Remove Spaces in Excel

Remove Spaces with TRIM Function

Excel has several functions and formulas that can aid in performing various operations. The TRIM function in Excel removes any unnecessary spaces between texts.

This function removes excess leading and trailing spaces between values but leaves a single space between the texts.

To remove spaces with the Excel TRIM function, follow the steps below:

1. Create a column for trimmed text.

2. Select a cell, then type the TRIM formula in the formula bar to remove multiple spaces.

3. Use the drop-fill handle to apply the formula to remove all the spaces for all the remaining cells.

   

The function produces trimmed values in a helper column. You can overwrite the original untrimmed values by copying and pasting the trimmed values into the target location.

To copy trimmed spaces and removed values in a selected column, right-click and select cut/copy. When pasting, use the "paste special" function and paste the trimmed spaces as values.

Note: The Excel TRIM function can remove only the leading spaces of a text string while keeping all spaces in the middle intact.

Check out our basic Microsoft Excel formula guide to learn more about the TRIM function. 

Remove Line Breaks and Non-Printing Characters

Importing data from external sources can include non-printing characters such as carriage return, a line break, feed, vertical or horizontal tab, etc., and extra spaces.

Although the TRIM function can remove spaces, it cannot remove non-printing characters. This feature can remove only the value 32 in the 7-bit ASCII system, representing the space character.

You can combine TRIM and CLEAN functions to delete non-printing characters and spaces from a string. CLEAN is mainly used for data cleaning and can eliminate the first 32 non-printing characters in the 7-bit ASCII set (values 0 through 31), including line breaks (value 10).

If the data you need to clean is in cell B2, write this formula in the image:

Remove Non-breaking Spaces

If the TRIM + CLEAN formula does not eliminate all spaces in-between space, you might've copied and pasted data from another place. Sometimes, non-breaking spaces come along with the data.

To remove nonbreaking spaces (HTML character  ), substitute them with regular spaces, and then apply the TRIM function:

To understand the formula better, let's look at it in pieces:

• A non-breaking character is a special character. It has a value of 160 in the 7-bit ASCII system. Use the CHAR(160) formula to add it to your work.

• The SUBSTITUTE function is like a special tool. It can change spaces that don't break into common spaces.

• Finally, add the SUBSTITUTE statement to the TRIM function, which will help you eliminate extra spaces.

If your worksheet has characters that do not print out, use the CLEAN function with TRIM and SUBSTITUTE to eliminate spaces and symbols you don't want all at once:

The image shows what the functions do:

Remove All Spaces

Sometimes, you may need to delete all spaces in a cell, including single spaces between words or numbers.

This function is useful when dealing with imported numeric columns that use spaces as thousands of separators. The feature makes it easier to read large numbers while avoiding calculations by formulas.

To delete all spaces simultaneously, use SUBSTITUTE, as mentioned in the previous example. The only difference is that you should replace the space character (returned by CHAR(32)) with an empty string (").

To include a space in the formula, you can type " " instead:

Select a cell, put the formula, then use the drop-fill function to see the results.

You can use the TRIM formula to remove each extra space from cells by using the information about the number of spaces in each cell that you now have.

We share more details about the SUBSTITUTE function in "Excel: Remove Trailing Spaces Quickly and Easily With These Simple Steps."

Remove Spaces with Find and Replace

Here is an alternative method for removing excessive spaces between words or numbers. This technique involves discovering and replacing multiple spaces with a single space.

However, it leaves one space with leading, trailing, or inside gaps. This approach is particularly useful when cleaning up data in a spreadsheet with many untrimmed values spread out.

This method has the advantage of replacing the values in their original position, eliminating the need for an extra empty column to accommodate the trimmed values.

1. Select the cells.

   

2. Go to the Home tab, under the Editing group, and click the Find and Select tool. Select Replace from the options.

3. In the Find and Replace dialog box, type double-space in the Find What box. In the Replace With box, type single space. Click the Replace All and OK buttons in the Microsoft Excel pop-up box.

   

   When the "No more replacements" box pops up, the selected cells have no more space.

Final Thoughts on Remove Spaces in Excel

Learning to remove spaces in Excel saves you time when compiling data and preparing for presentations. Moreover, it can also help improve the accuracy of your findings. With the right knowledge, you can easily move unwanted spaces and ensure your spreadsheet data is clean and precise.

For more easy-to-follow guides, visit Simple Sheets and the Related Articles section of this blog post. 

Subscribe to Simple Sheets on Youtube for the most straightforward Excel video tutorials!

Frequently Asked Questions on Remove Spaces in Excel

What function should I use when removing spaces when TRIM, CLEAN, and SUBSTITUTE are not working?

If the TRIM, CLEAN, and SUBSTITUTE functions are not working, it means those characters have ASCII values different from 0 to 32 "non-printing characters" or 160 "Non-Breaking Space characters." Use the CODE function to find the special characters. Then, input SUBSTITUTE to change it into a common space, and use TRIM to remove any extra spaces.

Can I use the Find and Replace tool for numerical values?

The Find and Replace tool helps remove spaces in numbers, especially with large numbers separated by spaces. After all, we can consider the extra spaces hiding an obstacle in this scenario.

What if the TRIM and CLEAN formula doesn't work? What should I do?

Use the Replace All feature to replace line breaks with spaces in the Excel cell, and do the following steps below:

1. Press Ctrl+J in the Find What box and space in the Replace with box.

2. Then, click Replace All to replace all line breaks with spaces in the selected range.

Related Articles:

Microsoft Excel Uses: Effective Ways to Use Microsoft Excel

How to Get Microsoft Excel for Mac

Learn How to Split Cells in Excel Quick and Easy