How to Remove Extra Spaces From Data in Google Spreadsheets

When text data is imported or copied into a Google spreadsheet, extra spaces are sometimes included along with the text data.

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.

1. If you are using your own data, select the worksheet cell where you want the trimmed data to reside.

2. If you are following this example, select cell A6 to make it the active cell in which you will enter the TRIM function and where the edited text will be displayed.

3. Type the equal sign ( = ) followed by the name of the function (TRIM).

   As you type, the auto-suggest box appears with the names of functions that begin with the letter T. When  TRIM appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell A6.

4. The argument for the TRIM function is entered after the open round bracket.

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.

1. Click on cell A1 in the worksheet to enter this cell reference as the text argument.

2. Press the Enter key on the keyboard to enter a closing round bracket after the function's argument and to complete the function.

3. The line of text from cell A1 should appear in cell A6, but with only one space between each word. When you click on cell A6 the complete function = TRIM ( A1 ) appears in the formula bar above the worksheet.

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.

1. Select cell A6 to make it the active cell.

2. Place the mouse pointer over the black square in the bottom right corner of cell A6; the pointer will change to a plus sign.

3. Click and hold down the left mouse button and drag the fill handle down to cell A8.

4. Release the mouse button. Cells A7 and A8 should contain trimmed lines of text from 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.

1. Highlight cells A6 to A8 in the worksheet.

2. Copy the data in these cells using Ctrl+C on the keyboard or Edit > Copy from the menus. The three cells should be outlined with a dashed border to indicate they are being copied.

3. Select cell A1.

4. Select Edit > Paste special > Paste values only to paste only the TRIM function results into cells A1 to A3.

5. The trimmed text should be present in cells A1 to A3 as well as cells A6 to A8

6. Highlight cells A6 to A8 in the worksheet.

7. Press the Delete key on the keyboard to delete the three TRIM functions.

8. The trimmed data should still be present in cells A1 to A3 after deleting the functions.