# TRIM and CLEAN in Google Sheets for Imported Text

- Author: FastBankConvert (https://www.fastbankconvert.com)
- Published: 2025-02-14
- Updated: 2025-02-14
- Category: Google Sheets, Numbers and LibreOffice
- Canonical: https://www.fastbankconvert.com/blog/trim-and-clean-in-google-sheets

> Use TRIM and CLEAN in Google Sheets on imported PDF text: extra spaces, non-breaking spaces, hidden characters, and the trim tool.
TRIM and CLEAN in Google Sheets fix different problems in imported text. TRIM removes spaces at the start and end of a cell and squeezes repeated spaces to one. CLEAN removes invisible ASCII control characters. Neither touches non-breaking spaces, which PDF text often contains, so pair them with SUBSTITUTE for a complete cleanup.

## What does TRIM change in Google Sheets text?

TRIM in Google Sheets removes leading spaces, trailing spaces and repeated spaces, leaving a single space between words. Google's [TRIM function reference](https://support.google.com/docs/answer/3094140) gives the syntax as `TRIM(text)`. It works on ordinary space characters only. A cell that looks clean after TRIM can still hold other kinds of whitespace.

What TRIM does to example values, with dots standing for spaces:

| Before | After TRIM |
| --- | --- |
| `..GROCERY MART` | `GROCERY MART` |
| `GROCERY MART..` | `GROCERY MART` |
| `GROCERY...MART` | `GROCERY MART` |

The reference adds a detail that explains why imports need it. Sheets trims text you type into a cell by default. Text that arrives by paste, import or formula keeps its spaces, so a description pasted from a PDF can carry a trailing space that a typed one would not.

Those spaces matter more than they look. Two descriptions that differ only by a trailing space count as different values in a lookup, a pivot table or a filter. The reference says this directly about formulas and data validation: spaces before or after text are significant. A rule that matches "GROCERY MART" will miss "GROCERY MART " every time.

TRIM does not touch spaces you want. A single space between two words stays. It also leaves line breaks inside a cell, which are a different character.

To see how much TRIM would change across a whole column before you change anything, compare total lengths. In an empty cell, `=SUMPRODUCT(LEN(B2:B500)-LEN(TRIM(B2:B500)))` returns the number of ordinary spaces TRIM would remove from those rows. A result of zero means spaces are not your problem, and the hidden characters in the next two sections are the more likely cause of a failed match.

## What does CLEAN remove from imported Google Sheets cells?

CLEAN in Google Sheets returns text with the non-printable ASCII characters removed. These are control codes such as tabs, line feeds and other characters below code 32 that some systems write into exports. Google's [CLEAN function reference](https://support.google.com/docs/answer/3267340) notes that non-printable Unicode characters outside ASCII are not removed.

The same page warns that Sheets does not show non-printable characters, so using CLEAN usually produces no visible change. That is the point. You cannot see the character, but a lookup, a comparison or a number conversion fails because of it.

Where control characters come from in statement data:

- **Copying from a PDF viewer**, which can insert a line feed at the end of each printed line, or a tab between columns.
- **Exports from older banking systems**, which sometimes pad fields with control codes.
- **Descriptions that wrapped onto two lines** on the statement, joined back together with a line break inside the cell.

CLEAN removes all of these from ASCII text. A line feed inside a cell is a common case: CLEAN deletes it, which joins the two parts without a space. If the parts need a space between them, replace the line break with a space first using `SUBSTITUTE(A2,CHAR(10)," ")`, then clean.

## Non-breaking spaces that TRIM and CLEAN both leave behind

Non-breaking spaces survive both TRIM and CLEAN in Google Sheets. A non-breaking space is character 160, which looks like an ordinary space but is a different code. The TRIM reference states that non-breaking space will not be trimmed, and CLEAN only removes control characters. Replace them with SUBSTITUTE and CHAR(160).

PDF text is a frequent source. Some PDF producers place a non-breaking space between an amount and its currency sign, inside a date, or between the words of a fixed column heading. Copy that text into Sheets and the character comes with it.

### Finding the hidden character

LEN counts every character, including invisible ones, so comparing lengths tells you something is there. The table is a worked example with made-up cells from a pasted statement.

| Cell as it looks | LEN | What is hidden | Fix |
| --- | --- | --- | --- |
| `GROCERY MART` | 13 | A trailing space | `TRIM(A2)` |
| `GROCERY MART` | 13 | A trailing non-breaking space | `TRIM(SUBSTITUTE(A3,CHAR(160)," "))` |
| `CITY WATER` | 11 | A line feed at the end | `CLEAN(A4)` |
| `52.18` | 6 | A non-breaking space before the digits | `SUBSTITUTE(A5,CHAR(160),"")` |

The first two rows look identical and have the same length, yet only one of them responds to TRIM. To tell them apart, use `=CODE(RIGHT(A2,1))`. It returns 32 for an ordinary space and 160 for a non-breaking one.

Other Unicode characters behave the same way. A zero-width space, character 8203, has no width at all and cannot be seen even with the cursor beside it. CLEAN leaves it because it is not ASCII. Remove it with `SUBSTITUTE(A2,CHAR(8203),"")`.

## One TRIM and CLEAN formula for a whole Google Sheets column

To run TRIM and CLEAN over a whole Google Sheets column at once, nest them inside ARRAYFORMULA in the first cell of a helper column. One formula then fills every row below it. Add SUBSTITUTE for non-breaking spaces in the same formula, so a single pass handles all three problems.

For descriptions in column B starting at row 2, put this in cell E2:

```
=ARRAYFORMULA(IF(B2:B="",,TRIM(CLEAN(SUBSTITUTE(B2:B,CHAR(160)," ")))))
```

Read it from the inside out:

1. **SUBSTITUTE** turns every non-breaking space into an ordinary space.
2. **CLEAN** removes control characters such as line feeds.
3. **TRIM** removes the leading, trailing and doubled spaces the first two steps leave behind.
4. **IF** leaves blank rows blank, so the formula does not write empty strings down the whole sheet.
5. **ARRAYFORMULA** applies it to every row in the range.

The order matters. Replacing non-breaking spaces before TRIM means TRIM can then remove them. Running TRIM first would leave them in place.

Once the helper column looks right, copy it and use Edit, then Paste special, then Values only over the original column. Then delete the helper column. Keeping the formula instead is fine for a live import that refreshes, but a static statement does not need it, and values are faster to sort and filter.

If you need to pull parts out of the cleaned text, such as a store number or a reference code, a pattern match is the next step. [REGEXEXTRACT in Google Sheets](/blog/regexextract-in-google-sheets) shows patterns for that kind of field.

## The Trim whitespace menu tool versus the TRIM formula

The Trim whitespace menu tool cleans the selected cells in place, while the TRIM formula writes cleaned text into another cell. Select a range and choose Data, then Data cleanup, then Trim whitespace. Google's page on [splitting text, removing duplicates or trimming whitespace](https://support.google.com/docs/answer/6325535?hl=en&co=GENIE.Platform%3DDesktop) says it removes leading, trailing and excessive spaces, and does not trim non-breaking spaces.

How to choose between them:

| Situation | Use |
| --- | --- |
| A one-off paste you want fixed now | Trim whitespace menu tool |
| Data refreshed by IMPORTRANGE or another formula | TRIM in a helper column |
| Text with control characters or non-breaking spaces | The combined formula above |
| You want to keep the original text for checking | TRIM in a helper column |

The menu tool is the faster option for a statement pasted once. It changes the cells directly, so there is no helper column to clear up. It also cannot reach characters other than ordinary spaces, so run a LEN check on a few cells afterwards.

The formula route keeps the raw data intact next to the cleaned version until you choose to replace it. That is useful when you are not yet sure what the import contains, and it lets you compare before and after row by row.

## Trimmed amounts that still refuse to sum in Google Sheets

Trimmed amounts can still refuse to sum in Google Sheets, because TRIM and CLEAN return text. A cleaned "52.18" is a tidy string, not a number. Wrap the cleaned value in VALUE, or multiply by one, to turn it into a number that SUM, sorting and charts can use.

The fix for an amount column in C:

```
=ARRAYFORMULA(IF(C2:C="",,VALUE(TRIM(SUBSTITUTE(C2:C,CHAR(160),"")))))
```

Here SUBSTITUTE removes non-breaking spaces entirely, rather than turning them into ordinary spaces, because an amount should hold no spaces at all.

VALUE still fails on characters that are not whitespace. Currency signs, thousands separators in the wrong locale, trailing minus signs and parentheses around negatives all need their own treatment. [Negative numbers in parentheses in Google Sheets](/blog/negative-numbers-in-parentheses-in-google-sheets) covers turning "(52.18)" into -52.18.

After converting, check the column against the statement. A total that matches the printed total, or a running balance that ties row by row, proves the cleanup lost nothing. Statements that print subtotals per page or per day need those rows removed before the total means anything; [converting a PDF table with subtotals to Excel](/blog/pdf-table-with-subtotals-to-excel) explains how to find and drop them.

Cleaner input shortens all of this. FastBankConvert is being built to read bank statement PDFs into Excel, CSV or JSON with amounts in their own columns, and to tell you before download if the balances do not add up. It is getting ready to open, and you can join at [Get started](/get-started).