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.

Click Data, Trim Whitespace in Google Sheets

Advertisement

You’ll see a pop-up message letting you know the number of cells that were trimmed. Click “OK.”

Selected cells trimmed message

And just like that, those extra spaces vanish!

Whitespace trimmed in Google Sheets

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.

Trim Cells

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.

=TRIM(A2)

Trim function for a cell

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.

Trim function for a cell result

Trim Text

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   ")

Trim function for text

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.

Trim function for text result

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.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.