Excel + Google Sheets · TRIM
Remove extra spaces from any text
TRIM deletes spaces at the start and end of a text and leaves a single space between words. In the example each · is a space.
Extra spaces are invisible and they break more spreadsheets than any other small problem. A name copied from a website, an export from another program or a cell typed in a hurry often carries a space at the start or the end. Then a lookup says the value does not exist, even though you can see it right there.
TRIM removes those spaces for you. It works in Excel 2010 or later, in Excel 365 and in Google Sheets. It is the first step of cleaning any imported column, before you use MATCH or XLOOKUP. To fix capital letters at the same time, see the TRIM and PROPER guide.
How it works TRIM
TRIM takes just one argument, the text to clean. Write the formula like this:
=TRIM(A2)Excel deletes the spaces before and after the text and reduces any run of spaces between words to a single space. The original cell stays untouched, so you can compare both columns before replacing anything.
Example step by step
- Paste the names in column A, starting at A2.
- Click B2, type
=TRIM(A2)and press Enter. - Drag the small square in the corner of B2 down to copy the formula.
- Copy column B and use Paste values only over column A if you want to keep the clean text.
| Imported | Clean |
|---|---|
| ·Laura···Gomez· | Laura Gomez |
| João··Silva | João Silva |
| ·Priya·Rao·· | Priya Rao |
| Tom···Reed | Tom Reed |
In the table every space is shown as a dot to make it visible. The clean column has no dot at the start or the end, and only one between the words, so Laura Gomez is now written exactly like any other Laura Gomez.
Common errors
- The text still looks wrong after TRIM because the space is a non-breaking space, common in web copies. Replace it first with SUBSTITUTE.
- The result is pasted over the original with a normal paste and the formulas break. Use Paste values only.
- Numbers become text after TRIM, so sums ignore them. Multiply by 1 or use VALUE to turn them back.
- The formula is applied to the header row. Start at the first data row.
Make TRIM a habit: every column that comes from outside should pass through it before you build formulas on top. It costs one column and saves hours of looking for a mistake that cannot be seen. TRIM works in Excel 2010 or later, Excel 365 and Google Sheets.