# How to remove non-printing characters in Excel

Use =TRIM(CLEAN(A2)). CLEAN removes the invisible control characters (codes 0 to 31) and TRIM removes extra spaces.

**Excel:** `=TRIM(CLEAN(A2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Imported text | Clean |
| INV-1042 |  |
| Contoso Ltd |  |
| Northwind Traders |  |
| Fabrikam Inc |  |
| Litware |  |

Formula in B2, filled to B6: `=TRIM(CLEAN(A2))`

Result: INV-1042; Contoso Ltd; Northwind Traders; Fabrikam Inc; Litware

## How it works

Exports from databases and other systems can carry tabs, carriage returns and other control characters you can't see. They make `"INV-1042"` and `"INV-1042"` plus a tab different values, so lookups fail. CLEAN strips characters 0 to 31, and TRIM tidies the spaces that remain.

## Other ways

- `=CLEAN(A2)`: CLEAN alone, when you know there are no extra spaces.

## Common mistakes

- CLEAN doesn't remove the non-breaking space (character 160) or Unicode characters like the zero-width space. Use SUBSTITUTE for those.
- CLEAN removes line breaks too, joining the words on either side. Replace CHAR(10) with a space first if the lines should stay apart.

## Questions

**How do I find out which invisible character is there?**

Check its code: `=CODE(RIGHT(A2))` for the last character, or `=CODE(MID(A2,5,1))` for the fifth.

**How do I count the hidden characters?**

Compare lengths: `=LEN(A2)-LEN(CLEAN(A2))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remove-non-printing-characters