On a computer, a space between words is not a blank area but a character, and these extra characters can affect how data is used in a worksheet — such as in the CONCATENATE function, which combines multiple cells of data into one. Rather than manually editing the data to remove the unwanted spaces, use the TRIM function to remove the extra spaces from between the words or other text strings.
Google Spreadsheets’ TRIM Function
A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments. The syntax for the TRIM function is: TRIM(text) The argument for the TRIM function is: Text (Required) This is the data from which you want to remove spaces. This can be:
The actual data to be trimmed. The cell reference to the location of the text data in the worksheet.
Removing the Original Data With Paste Special
If the cell reference to the location of the data to be trimmed is used as the text argument, the function cannot reside in the same cell as the original data. As a result, the affected text must remain in its original location in the worksheet. This can present problems if there is a large amount of trimmed data or if the original data is located in an important work area. One way around this problem is to use Paste Special to paste values only after data has been copied. This means that the TRIM function’s results can be pasted back on top of the original data, and then the TRIM function removed.
Example: Remove Extra Spaces With the TRIM Function
This example includes the steps necessary to:
Remove extra spaces from between three lines of text in rows 1 to 3 in the worksheet, as shown in the image above.Copy and paste special used to replace the original data in the first three rows.Use the TRIM function to remove the extra spaces.
Entering the Tutorial Data
Open a Google Spreadsheet that has text containing additional spaces that need to be removed, or copy and paste the lines below into cells A1 to A3 into a worksheet.
Entering the Function’s Argument
Google Spreadsheets does not use dialog boxes to enter a function’s arguments, as Excel does. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.
Copying the Function With the Fill Handle
The fill handle is used to copy the TRIM function in cell A6 to cells A7 and A8 to remove the extra spaces from the lines of text in cells A2 and A3.
How to Remove the Original Data With Paste Special
The original data in cells A1 to A3 can be removed without affecting the trimmed data by using paste special’s paste values option to paste over the original data in cells A1 to A3. Following that, the TRIM functions in cells A6 to A8 also will be removed because they are no longer needed. #REF! errors: If you use a regular copy and paste operation instead of paste values, the TRIM functions will be pasted into cells A1 to A3, which will result in numerous #REF! errors being displayed in the worksheet.
