TRIM Function in Excel and Google Sheets
Purpose
Remove all leading, trailing, and excess internal spaces from a text string
Return value
Text with leading and trailing spaces removed and multiple internal spaces reduced to one
TRIM is the first function I run on every column of data arriving from an external system before doing anything else with it. Data from databases, CRM exports, ERP systems, and CSV files almost always contains invisible space characters that cause enormous problems downstream. A customer name with a leading space will not match the same name in a lookup table. A product code with a trailing space will cause COUNTIF to return zero even when the code clearly exists. VLOOKUP returns #N/A. Exact text comparisons return FALSE. Conditional formatting rules miss the rows they should highlight. All of these failures can be traced back to spaces that are invisible on screen but present in the cell value. TRIM eliminates them in a single formula. It removes every space before the first visible character, every space after the last visible character, and reduces any run of multiple spaces between words to exactly one space. It is not a glamorous function but it is the foundation of clean, reliable data and the first step in any professional data preparation workflow.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=TRIM(text)
Arguments
| Argument | Required | Description |
|---|---|---|
| text | Required | The text string or cell reference to clean. TRIM removes all leading spaces, all trailing spaces, and reduces any run of multiple internal spaces to a single space. Does not remove line breaks or non-breaking spaces. |
How it works
TRIM takes a single text argument and returns the cleaned version. Apply it by wrapping any cell reference: =TRIM(A2). The cleaned text is the result and can be used directly inside other formulas without creating a separate helper column. You can nest TRIM inside VLOOKUP to clean the lookup value on the fly: =VLOOKUP(TRIM(A2),Table,2,FALSE). You can use TRIM inside EXACT to compare two cells that should match but might differ due to spaces. The most important thing to understand about TRIM is what it does not remove: line breaks created by Alt+Enter are not spaces and are not removed by TRIM — use CLEAN for those. Non-breaking spaces from web content (CHAR 160) look like spaces but have a different character code and are not removed by TRIM alone — combine TRIM with SUBSTITUTE to handle both. To make the cleaning permanent rather than formula-driven, copy the TRIM column and paste as Values Only over the original data. This converts the formula results to static text and removes the dependency on the original unclean column.
Examples
1
Clean imported names that have leading and trailing spaces causing COUNTIF to miss matches.
| A | B | |
|---|---|---|
| 1 | Raw Name | Cleaned |
| 2 | Alice Chen | =TRIM(A2) |
| 3 | Bob Smith | Bob Smith |
| 4 | Carol Jones | Carol Jones |
| 5 | David Kim | David Kim |
| 6 | Eve Taylor | Eve Taylor |
Row 2: Alice Chen
— Alice Chen becomes "Alice Chen" — 2 leading spaces and 2 trailing spaces removed.
David Kim has two spaces between first and last name. TRIM reduces them to one, giving "David Kim". TRIM never removes the single space between words — only extras beyond one.
2
Fix VLOOKUP returning #N/A by trimming the lookup value inline.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Raw ID | Cleaned Match | Code | Name | Price |
| 2 | PRD-003 | Monitor Stand | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-001 | Wireless Mouse | PRD-002 | USB Keyboard | 45.99 |
| 4 | PRD-005 | HDMI Cable | PRD-003 | Monitor Stand | 89.50 |
| 5 | PRD-004 | Laptop Bag | 59.99 | ||
| 6 | PRD-005 | HDMI Cable | 12.99 |
Row 2: Monitor Stand
— PRD-003 is cleaned to "PRD-003" before VLOOKUP searches — it now finds Monitor Stand.
Without TRIM, " PRD-003 " would not match "PRD-003" in the table and VLOOKUP would return #N/A. No source data cleaning required.
Common use cases
1. Clean all customer names imported from a CRM or database before using them in VLOOKUP or exact comparisons
=TRIM(A2)
2. Fix VLOOKUP #N/A errors caused by spaces in the lookup value without modifying the source table data
=VLOOKUP(TRIM(A2),Table,2,FALSE)
3. Compare two cells that should be identical but EXACT returns FALSE due to invisible space differences
=EXACT(TRIM(A2),TRIM(B2))
4. Remove both regular spaces and non-breaking spaces from content copied from a web page or PDF
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
5. Clean data before using COUNTIF to ensure matches are not missed due to leading or trailing spaces
=COUNTIF(B:B,TRIM(A2))
Common errors
TRIM does not remove all spaces — some remain after applying the formula
The remaining spaces are non-breaking spaces with character code 160 rather than regular spaces with code 32. These come from HTML content, PDF text extraction, and some export systems. They look identical to regular spaces but TRIM does not recognise them.
Fix: Add SUBSTITUTE to replace non-breaking spaces with regular spaces before TRIM processes them: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). This handles both types in one formula.
TRIM does not remove line breaks between lines within a cell
TRIM only removes space characters (ASCII 32). Line breaks created by pressing Alt+Enter inside a cell are character code 10 on Windows, which TRIM does not touch at all.
Fix: Use CLEAN to remove line breaks and other non-printable characters: =TRIM(CLEAN(A2)). Combining TRIM and CLEAN handles the most common data quality issues from external sources.
The cleaned data reverts after saving or refreshing
TRIM is a formula that returns a result — it does not permanently modify the original cell. If the source data changes, the TRIM result updates. If you need the cleaned version to be permanent and independent of the source data, you must paste as values.
Fix: Copy the column of TRIM formulas, then use Paste Special → Values Only to overwrite the original data column with the cleaned static text. The TRIM formulas can then be deleted.
Tips and variations
Combine TRIM and CLEAN for comprehensive text cleaning from external sources
CLEAN removes non-printable characters including line breaks from Alt+Enter, corrupted import characters, and other hidden formatting codes. TRIM removes extra spaces. Used together they address the two most common data quality issues from external data sources in a single formula.
=TRIM(CLEAN(A2))
Use TRIM inline inside VLOOKUP to fix #N/A without cleaning the source data
When the source table cannot be modified — because it is shared, protected, or regularly refreshed from an external system — wrap just the lookup value in TRIM so any spaces in the search term are removed before the match is attempted. The table data is untouched.
=VLOOKUP(TRIM(A2),$D:$F,2,FALSE)
Remove non-breaking spaces from web-pasted content with SUBSTITUTE
When content is pasted from a web browser or extracted from a PDF, non-breaking spaces (CHAR 160) are almost always present. TRIM alone will not remove them. SUBSTITUTE replaces them with regular spaces first, then TRIM removes any resulting extra spaces at the edges and between words.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Excel vs Google Sheets
Excel vs Google Sheets
TRIM behaves identically in Excel and Google Sheets — same character removed (ASCII 32), same behaviour for all space positions. Both handle non-breaking spaces the same way. Formulas copy between the two without any changes.
Related reading
Related Formulas
Frequently asked questions
TRIM removes all leading spaces from before the first character of a text string, all trailing spaces from after the last character, and reduces any sequence of multiple consecutive spaces between words to a single space. It does not remove line breaks, tab characters, or non-breaking spaces — those require CLEAN and SUBSTITUTE respectively. TRIM works only on standard space characters with the ASCII code 32.
TRIM only removes standard space characters (ASCII code 32). Non-breaking spaces with ASCII code 160 are used in HTML and appear in content copied from web pages, PDFs, and some export systems. They look completely identical to regular spaces on screen but TRIM does not recognise them. To remove both types, use SUBSTITUTE first to replace non-breaking spaces with regular spaces, then apply TRIM: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")).
Yes — extra spaces in lookup values or in the first column of a VLOOKUP table are one of the most frequent causes of #N/A errors that are difficult to diagnose. The values look identical on screen but fail exact matching because of invisible characters. Wrapping the lookup value in TRIM fixes the mismatch: =VLOOKUP(TRIM(A2),Table,2,FALSE). This cleans the search term before VLOOKUP uses it without requiring you to modify the source data.
Create a helper column next to your data and write =TRIM(A2) in the first row, then copy it down to cover all rows. Select the entire helper column, copy it, then paste using Paste Special with Values Only (Ctrl+Shift+V in Google Sheets, Ctrl+Alt+V then V in Excel) over the original data column. Delete the helper column. The cleaned text is now stored permanently as values and the TRIM formulas are gone.
Yes — TRIM behaves identically in both applications. It removes the same characters using the same logic and handles leading, trailing, and internal spaces the same way. Non-breaking spaces (CHAR 160) are not removed by TRIM in either application and require SUBSTITUTE in both. Formulas using TRIM copy between Excel and Google Sheets without any changes.