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.

of 02

Google Spreadsheets' TRIM Function

Google Spreadsheets
Google Spreadsheets' TRIM Function. Ted French

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, which is the data you want to remove spaces from. This can be:

Note: If the actual data to be trimmed is used as the text argument, it must be enclosed in quotation marks, such as:

=TRIM("Remove extra spaces")

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 originally 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 extra spaces that need to be removed, or copy and paste the lines below into cells A1 to A3 into a worksheet.

Row 1 of data with extra spaces

Row 2 of data with extra spaces

Row 3 of data with extra spaces

of 02

Entering the TRIM Function

Entering the TRIM Function Argument in Google Spreadsheets
Entering the TRIM Function Argument. Ted French

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. If you are using your own data, click on the worksheet cell where you want the trimmed data to reside.
  2. if you are following this example, click on cell A6 to make it the active cell. This is where the TRIM function will be entered and where the edited text will be displayed.
  3. Type the equal sign ( = ) followed by the name of the function (TRIM).
  4. As you type, the auto-suggest box appears with the names of functions that begin with the letter T.
  5. 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.

Entering the Function's Argument

As seen in the image above, the argument for the TRIM function is entered after the open round bracket.

  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.
  4. 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. Click on 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.

Removing 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. Click on cell A1.
  4. Click on Edit > Paste special > Paste values only in the menus 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.