Google Sheets Cheat Sheet

TRIM Google Sheets Function [With Quiz]

In Google Sheets the TRIM function removes leading, trailing, and repeated spaces from text.

In other words, it removes all spaces from the text, leaving a single space between words.

TRIM Syntax

=TRIM(text)

This function is useful for cleaning up user-submitted text to a consistent format.

For some reason people often add unnecessary spaces when entering text into fields. If you don't account for this, other functions and formulas in your sheet can throw errors.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

TRIM Function Example

ABC
1TextFormulaOutput
2 Pete   Miller  =TRIM(A2)Pete Miller
3    100        Example   St  =TRIM(A3)100 Example Street
4 Scranton,  PA  =TRIM(A4)PA

You can also reference text directly instead of using a cell reference:

=TRIM(" 100 Example St ")

Will output: 100 Example Street

Using The TRIM Function On A Whole Column

By combining the TRIM function and the ArrayFormula function you can force TRIM to accept more than one argument.

This means you can clean multiple cells of text, an entire column or row, or even a table with only one formula.

Here's an example using the same data as above:

ABC
1TextFormulaOutput
2 Pete   Miller  =ArrayFormula(TRIM(A2:A4))Pete Miller
3    100        Example   St  100 Example Street
4  PA  PA

All of the text has the unnecessary spaces removed using a single formula.

TRIM Errors

TRIM is a stable function, handling the following without error:

For images in cells and sparklines, TRIM returns a blank cell.

If you input a range (e.g. A2:A4) without using ArrayFormula it will output one piece of cleaned text if the range crosses the same row or column as the formula's cell.

Otherwise, it will throw a #VALUE! error:

An array value could not be found.

TRIM requires exactly one argument (a value, cell reference, or range reference).

If you have no arguments or more than one you will receive a #N/A error:

Wrong number of arguments to TRIM. Expected 1 arguments, but got 2 arguments.

TRIM Function Quiz

Here's a quick summary of how to use the quiz spreadsheet:

  1. Select the required cell while the formula bar is empty
  2. Type your answer in the formula bar
  3. You can click on the spreadsheet to add cell/range references

Complete all questions to solidify your understanding of the TRIM function.

TRIM Function Exercises
Enter a formula for each question in the formula bar for the spreadsheet below.
1. Single Cell
In cell B2, enter a formula to trim the name from the first column. Remember to start your formula with '='.
2. Whole Column
In cell C2, enter a formula that changes every item in the list to lowercase using a range reference. Remember, the LOWER function won't do this by itself.
ABC
1OriginalTrimmed CellTrimmed Column
2 Jim   Halpert  
3    363        Linden   Ave  
4 Scranton,  PA  
5
6
7
Click Here When Complete

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.