Have you ever had extra spaces appear in your Google Sheets data? Whether this happens after importing a file, mistakenly pressing the Space bar, or pasting data, unnecessary spaces in your text can turn into a problem.
If you use formulas or data validation in your sheet, extra spaces in the text can cause those things to either not work or not work correctly. Google Sheets gives you two quick ways to remove leading, repeated, and trailing spaces in your data, and we’ll show you how to use both.
Remove Spaces Using Trim Whitespace
If you want to remove the spaces within your text and keep that data in the existing cells, you can use the handy Trim Whitespace feature.
Select the cells with the text you want to remove the spaces from. Then click Data > Trim Whitespace from the menu.
You’ll see a pop-up message letting you know the number of cells that were trimmed. Click “OK.”
And just like that, those extra spaces vanish!
Remove Spaces Using the Trim Function
You can also use the TRIM function in Google Sheets to remove excess spaces in your text. With this function, you can select the cell containing the text or enter the text you want to adjust.
Select a cell where you want to place the function. This is also the cell where the result will display. Then enter the following replacing the A2 cell reference with your own.
Press Enter or Return and you’ll see the result in the function’s cell. It should display the text from your referenced cell without any extra spaces.
Another way to use the TRIM function is for specific text. As with the former, select a cell for the function and note that this places the result in that same cell. Then enter the following replacing the text with your own inside quotes.
=TRIM(" Marge Simpson ")
Press Enter or Return and you’ll see the result in the function’s cell. Again, this should provide you with text that doesn’t contain leading, repeated, or trailing spaces.
To avoid issues with validation or formulas in Google Sheets, or to simply ensure a consistent appearance, remember these methods for removing extra spaces in your text.