How to Trim Extra Spaces in Google Sheets

How to Trim Extra Spaces in Google Sheets

Use =TRIM(A2). It removes leading spaces, removes trailing spaces, and collapses any run of repeated spaces down to a single space. That handles the ordinary case in one step.

TRIM only recognises the standard space character, ASCII 32. If the cell still looks wrong after trimming, the culprit is a different character wearing a space costume. The most common one is CHAR(160), the non-breaking space that arrives with anything copied from a web page. This formula clears both in one pass:

Each section below answers one specific version of this problem, so you can jump to the one that matches your sheet.

How to Use the TRIM Function in Google Sheets

The TRIM function takes one argument, the text you want cleaned, and returns a new string. The syntax is TRIM(text), where text is either a cell reference or a quoted string.

That returns Acme Corp. The three leading spaces go, the three trailing spaces go, and the three spaces between the two words become one. The input is 17 characters and the output is 9.

TRIM works on one cell at a time. To clean a whole column without dragging the formula down, wrap it in ARRAYFORMULA:

The IF check matters. Without it, every empty row below your data returns an empty string rather than a true blank, which then breaks COUNTA and makes the column look full when it is not.

What the TRIM Function Does Not Remove

TRIM leaves four kinds of invisible character completely untouched, and every one of them will break an exact match. This is why a cell can look clean and still fail a VLOOKUP.

CLEAN handles two of these. It strips non-printable ASCII characters in the range 0 to 31, which covers the tab and the line break. It does nothing about CHAR(160) or CHAR(8203), because both sit outside that range. That gap is the single most common reason a trimmed cell still refuses to match.

How to Trim Whitespace in Google Sheets, Including Non-Breaking Spaces

To strip every kind of whitespace and keep single spaces between words, use REGEXREPLACE with a character class that names the non-breaking space explicitly:

Google Sheets uses the RE2 regex engine, where \s matches only the ASCII whitespace set: space, tab, newline, carriage return, and form feed. It does not match CHAR(160). Adding \x{00A0} to the class is what closes that hole, and the outer TRIM cleans up the edges afterwards.

If you would rather avoid regex entirely, nested SUBSTITUTE calls do the same job and are easier to read six months later:

How to Remove Leading and Trailing Spaces in Google Sheets

Plain TRIM removes leading and trailing spaces, but it also collapses the spaces inside your text. When the internal spacing has to survive exactly as typed, anchor a regex to the start and end of the string instead:

The ^ anchors the first half of the pattern to the beginning of the cell and the $ anchors the second half to the end. Everything in the middle is left alone, so a deliberately double-spaced address or a code block keeps its formatting.

How to Remove All Spaces in Google Sheets, Including Between Numbers

TRIM cannot do this, because it always preserves one space between items. To delete every space, use SUBSTITUTE with an empty replacement:

That turns 1 234 567 into 1234567. The result is still text, so wrap it in VALUE to get a number you can sum:

Space-separated thousands are a European formatting convention, and the separator is very often CHAR(160) rather than a plain space. That inner SUBSTITUTE is the reason the formula works on real exports and not just on typed examples. The same pattern cleans SKUs, phone numbers, and reference codes.

TRIM vs CLEAN in Google Sheets: Which One to Use

TRIM and CLEAN solve different halves of the problem and neither covers the other. TRIM targets spaces. CLEAN targets non-printable control characters. Pick by what you are trying to remove:

When whitespace is one step in a larger cleanup, it is usually worth handling it alongside the rest. See the full data normalization pass in Google Sheets for the sequence that catches casing and formatting at the same time.

How to Find Which Invisible Character Is Breaking Your Match

When two cells look identical but =A2=B2 returns FALSE, three formulas will identify the exact character in under a minute.

1. Count the characters. Run =LEN(A2) and compare it to the number of characters you can actually see. A name like Acme that reports 5 has one extra character hiding in it.
2. Isolate the suspect. Use =MID(A2, 5, 1) to pull out position 5 on its own. Change the position number until you land on the character that is not visible.
3. Identify it. Wrap that in CODE to get the number: =CODE(MID(A2, 5, 1)). A result of 32 is an ordinary space, 160 is a non-breaking space, 9 is a tab, and 8203 is a zero-width space.

Once you know the code, the fix is a single SUBSTITUTE against that CHAR value. This is also the fastest way to prove why a lookup fails before rebuilding the formula. The same invisible characters are behind most cases of messy CSV data in Google Sheets.

How to Trim Whitespace Without a Formula

Google Sheets has a built-in menu command that edits cells in place, with no helper column. Select your range, then choose Data, Data cleanup, Trim whitespace. It applies the same rule as the TRIM function and overwrites the original values.

For anything TRIM misses, use Find and replace instead. Open it with Ctrl+H, then tick Search using regular expressions. These three patterns cover most cleanups:

Find and replace rewrites the cells directly, so take a copy of the sheet first if the original values matter.

The FITS Way (Just Ask)

With FITS, you describe the clean result. The AI strips every kind of stray whitespace, not just the plain space character.

No CHAR codes to remember. Non-breaking spaces, tabs, and stray line breaks all go, because the instruction is about the outcome you want, not the exact characters to hunt for.

Trim Extra Spaces in Google Sheets: Frequently Asked Questions

Does TRIM remove all spaces in Google Sheets?

No. TRIM removes leading spaces, trailing spaces, and repeated spaces between words, but it always leaves one space between words. It also only handles the standard space character, ASCII 32. Tabs, line breaks, and non-breaking spaces survive TRIM untouched.

Why does TRIM not remove the space in my cell?

The character is almost certainly not a standard space. Text pasted from a website usually carries CHAR(160), the non-breaking space, which looks identical but is a different character. Use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) to convert it first, then trim.

How do I strip all whitespace in Google Sheets?

Use =REGEXREPLACE(A2, "[\s\x{00A0}]+", "") to delete every space, tab, line break, and non-breaking space. Use a single space as the replacement instead of an empty string if you want single spaces kept between words.

How do I remove spaces between numbers in Google Sheets?

TRIM cannot do this, because it preserves one space between items. Use =SUBSTITUTE(A2, " ", "") to delete every space character, which turns 1 234 567 into 1234567. Wrap the result in VALUE to convert it back to a real number.

What is the difference between TRIM and CLEAN in Google Sheets?

TRIM targets spaces, removing leading, trailing, and repeated ones. CLEAN targets non-printable ASCII characters in the range 0 to 31, which covers tabs and line breaks but no spaces at all. Neither removes CHAR(160), because 160 sits outside the range CLEAN handles.

How do I trim whitespace in an entire column in Google Sheets?

Wrap the formula in ARRAYFORMULA and point it at the range. =ARRAYFORMULA(IF(A2:A = "", "", TRIM(A2:A))) cleans the whole column from one cell. The IF check keeps blank rows blank instead of filling them with empty strings.

When to Use Each

If your data only has plain spaces, TRIM is free and instant. Reach for =FITS() when text was pasted from the web, when hidden characters keep breaking matches, or when trimming is one step in a bigger cleanup. Whitespace rarely arrives alone, so pair it with splitting names in Google Sheets, where a stray leading space silently shifts which word the formula treats as the first name, removing special characters, standardizing date formats, or a full data normalize pass. Cleanup is one job on a long list. Read the full guide to automating Google Sheets tasks you used to need regex for.