Microsoft Excel is a powerful tool that lets users organise, analyse and visualise information effectively. However, users encounter a common problem dealing with unnecessary spaces within cells. These spaces can stem from imported data, user input, or formatting issues. These extra spaces can cause problems with calculations, sorting and data analysis. Fortunately, Excel provides various ways to remove these spaces.
In this article, we have discussed various ways to remove spaces in an Excel worksheet in a few simple and easy steps.
Also read: How to count unique values in Excel?
Remove spaces using the Trim function
The trim function removes excess leading, trailing and spaces in-between values. However, a space remains between the two texts. You need an empty column to enter the formula and get the trimmed values.
To remove the additional spaces from the values, input the formula =TRIM(cell number) into the desired cell or the formula bar. In this context, the cell number represents the location of the original text that hasn’t been trimmed.
For Example: We have entered the formula =Trim(A2) to clear the leading space between the text values in cell A2.

Result: The leading, in-between or trailing spaces are trimmed.

Remove spaces in multiple cells
You can use the drag and handle to remove the spaces in other existing cells with untrimmed spaces.

This makes the trimmed values in the helper column. Copy and paste these trimmed values from the helper column to the main column.
Also read: Xlookup vs Vlookup: Brief Comparison
Remove non-printable characters with spaces
If some spaces remain after using the Trim function, there might be some additional non-printable characters. The trim function only removes spaces. To clear those non-printable characters, you have to use the Clean function, which will remove the non-printable characters in the text.

Also read: How to create a collapsible row in Excel?
Remove spaces in Excel using the Find and Replace
This method is based on finding the extra spaces and replacing them. However, it keeps one space on leading, trailing or in-between. This method works well when the values with spaces are scattered across the spreadsheet. One benefit of this approach is that it changes the values right where they are. You won’t need to make an extra empty column for the tidied-up values.
Follow the steps below to use the find and replace method to remove the spaces.
Step 1: Select the columns with the values with extra spaces and press Ctrl + H keys to open the Find and Replace dialogue box.
Step 2: In the Find What box, press the Space bar twice, then in the Replace With box, press the Space bar once and then click on the Replace All button.

Step 3: Click the OK button in the confirmation dialogue box.

Step 4: Click on Replace All button again until you get We couldn’t find anything to replace the dialogue box.

Also read: How to count cells with text in Excel?