A common need in Google Sheets is needing to remove extra whitespace characters, such as spaces and tabs, from text fields.
Example use cases
Spreadsheets often contain data sloppily entered by users who enter random spaces before and after the actual data. These extra spaces can interfere with further functions, so cleaning up these fields will reduce errors and increase accuracy of your spreadsheets.
Another scenario where removing whitespace is useful is combining multiple words into a single word. For example, you might have a list of names that you want to turn into emails, such as transforming “John Smith” into “JohnSmith@domain.com”. By removing whitespace from the middle of the text, we can achieve such results.
In this article, we show you a few ways for getting rid of that pesky whitespace.
Remove whitespace from the beginning and end of text with the TRIM() function
The TRIM() function removes leading and trailing spaces. Applying TRIM() will remove any spaces before the first non-whitespace character and any spaces after the last non-whitespace character.
The following formula produces “Remove spaces”:
=trim(" Remove spaces ")
Remove whitespace from the middle of text with the REGEXREPLACE() function
We can use REGEXREPLACE() to replace part of a text with different text. By specifying the regular expression for whitespace (“\s”), and replacing it with nothing (“”), we remove all the whitespace in the text.
The following formula produces “removeallwhitespace”:
=REGEXREPLACE(" remove all whitespace ","\s","")