TRIM Function in Google Sheets

If you want to scrub a column of messy text and get rid of stray spaces, the TRIM function in Google Sheets is the simplest way to do it.

It removes any leading and trailing spaces, and collapses runs of internal spaces down to single spaces. In this article, I’ll show you how to use TRIM to clean names, sentences, pasted emails, joined strings, and street addresses.

TRIM Function Syntax in Google Sheets

Here is how the TRIM function is written:

=TRIM(text)
  • text is the string or cell reference you want to clean.

TRIM only touches regular spaces. It does not remove tabs, line breaks, or non-breaking spaces (the kind you often pick up when pasting from a web page or a PDF). For those, see the Tips & Common Mistakes section below.

When to Use TRIM Function

  • Clean a column of names that have padding on either side.
  • Flatten extra spaces in pasted sentences down to a single space between words.
  • Tidy up emails and other strings copied from a web page or report.
  • Wrap a concatenation so trailing spaces in the source columns don’t leak into the joined result.
  • Make lookup keys match when a join fails because of stray spaces in one column.

Example 1: Strip Leading And Trailing Spaces From Names

Let’s start with the most common reason to use TRIM, cleaning up padded names.

Below is the dataset, with names in column A, across rows 2 to 7. Some rows have spaces before the name, some have spaces after, and a few have both.

Google Sheets: column A shows 'Name' in A1 and six example names in A2-A7.

You want a cleaned column where each name has no padding on either side.

Here is the formula:

=TRIM(A2)
Google Sheets: Cell B2 shows trimmed text with `=TRIM(A2)` in the formula bar.

TRIM scans the cell, drops any spaces at the start and end, and returns whatever’s left. The first row becomes Jessica Carter, with no padding on either side. The rest of the column follows the same pattern.

This is the form of TRIM you’ll use most often. Pasted data, exported CSVs, and form responses all tend to ship with a stray space or two on the edges, and TRIM cleans them out in one pass.

Example 2: Collapse Runs Of Internal Spaces In A Sentence

TRIM also handles spaces inside the string. Anywhere you have a run of two or more spaces between words, TRIM flattens it down to a single space.

Below is the dataset, with sentences in column A, across rows 2 to 6. Each sentence has multiple spaces sprinkled between words.

Google Sheets column A displays text examples in cells A2-A6 with extra spaces for the TRIM function tutorial.

You want each sentence rewritten with exactly one space between words.

Here is the formula:

=TRIM(A2)
Google Sheets showing `=TRIM(A2)` formula in B2 for removing extra spaces from text.

The formula is the same as before. The only difference is what TRIM has to clean up. Here, it finds runs of multiple spaces between words and shrinks each one back to a single space.

So the first row becomes Hello world this is a test, with no double spaces left anywhere. The same fix happens on every row, regardless of how many extra spaces were sprinkled in.

Example 3: Clean Up Email Addresses Pasted From Elsewhere

Pasted data tends to carry a mix of leading, trailing, and internal whitespace.

Below is the dataset, with email addresses in column A, across rows 2 to 7. Some rows have padding on the edges, some have a stray space inside the username, and a couple have both.

Google Sheets: Column A titled "Email" lists example email addresses with extra spaces.

You want a clean column of emails with no edge padding and no double spaces inside.

Here is the formula:

=TRIM(A2)
Google Sheets formula bar shows `=TRIM(A2)` for removing extra spaces from emails.

TRIM handles both edge and internal whitespace in one go. The first row becomes laura.kim@example.com. A row with david .lopez@example.com (four spaces in the middle) becomes david .lopez@example.com, since TRIM keeps one space rather than removing all of them.

That last bit is worth noting. TRIM does not delete every space, it only collapses runs of two or more down to one. If your data has unwanted single spaces inside, you need a different tool (SUBSTITUTE with " " and "").

Pro Tip: To count how many extra spaces are sitting in a cell, use [LEN](https://geosheets.com/google-sheets-function/len/) on the original and the trimmed version. The expression LEN(A2)-LEN(TRIM(A2)) tells you exactly how many characters TRIM stripped out.

Example 4: Join First And Last Names Without The Extra Space

TRIM shines when you wrap it around a concatenation.

Below is the dataset, with first names in column A and last names in column B, across rows 2 to 6. Several of the first-name cells have trailing spaces that we don’t want leaking into the joined result.

Google Sheets shows First Name and Last Name columns with five rows of name data.

You want a full-name column that joins first and last with exactly one space between them, no matter how much padding the source cells carry.

Here is the formula:

=TRIM(A2&" "&B2)
Google Sheet showing TRIM formula `=TRIM(A2&" "&B2)` in formula bar, result in C2.

How this formula works:

  • A2&" "&B2 joins the first name, a space, and the last name into one string.
  • If the first-name cell has trailing spaces, they sit between the two names in the joined result, giving you something like Olivia Bennett.
  • TRIM wraps that join and collapses any run of spaces between the names back to one.

The first row becomes Olivia Bennett, with a single space in the middle. Every other row joins cleanly the same way, even when the source cells have varying amounts of padding.

Example 5: Tidy Up Street Addresses With Messy Spacing

The last example brings the leading, trailing, and internal cleanup together on one dataset.

Below is the dataset, with street addresses in column A, across rows 2 to 6. Each row has a different combination of edge padding and double or triple spaces between words.

Google Sheets: Column A contains addresses, some with extra spaces.

You want a column of consistently-spaced addresses with no edge padding.

Here is the formula:

=TRIM(A2)
Google Sheets with formula =TRIM(A2) in B2, removing extra spaces from A2.

TRIM does its full job here. It removes the leading and trailing padding, and collapses every run of internal spaces. The first row becomes 221 Baker Street, with a single space between each pair of words.

This is the cleanup pass to run anytime you’ve pulled address data out of a PDF, a website, or another spreadsheet where the spacing was inconsistent. One TRIM and everything lines up.

Tips & Common Mistakes

  • TRIM does not remove non-breaking spaces. Text pasted from a web page or PDF often carries CHAR(160) instead of regular spaces, and TRIM leaves those alone. To clean them, strip the non-breaking space with SUBSTITUTE first: =TRIM(SUBSTITUTE(A2,CHAR(160),””)).
  • TRIM does not strip tabs or line breaks. It only handles the regular space character. For tabs and line breaks, use CLEAN inside TRIM, like =TRIM(CLEAN(A2)). CLEAN removes non-printing characters and TRIM finishes the job on the spaces.
  • Use TRIM before any lookup or join. If MATCH, VLOOKUP, or XLOOKUP returns #N/A on values that look identical, the culprit is usually invisible padding. Wrap both sides of the comparison in TRIM and the matches start working again.

TRIM is the first cleanup step you reach for whenever a column of text has uneven spacing. It strips the edges, collapses runs of internal spaces, and leaves the rest of the content untouched.

Pair it with CLEAN to also strip non-printing characters, and reach for SUBSTITUTE when you have to deal with non-breaking spaces or single internal spaces you don’t want.

List of All Google Sheets Functions

Related Google Sheets Functions / Articles: