Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Text

CLEAN

WISSEN.CONTROL in Dutch Excel

Removes the 32 non-printing control characters (codes 0 to 31) such as tabs and line breaks from a text.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C	D
1	Entry no. from export	Debit	Clean entry no.	Length
2	25-0412	€12,100.00€ 12.100,00	25-0412=CLEAN(A2)=CLEAN(A2)
3	25-0413	€3,025.00€ 3.025,00
4	25-0414	€450.00€ 450,00
5	25-0415	€9,680.00€ 9.680,00
6	25-0416	€1,815.50€ 1.815,50

Marks: line feedNote: Every entry number except A4 ends with a line feed, CHAR(10). A4 was keyed in by hand and is 7 characters; the others are 8.

Result: 25-0412

=CLEAN(A2)

=CLEAN(A2)

What CLEAN does and when to use it

What it does: it deletes the invisible control characters, such as tabs and line breaks, from a text, for example from entry numbers in a ledger export.

When to use it: when imported text misbehaves: a code that will not match, a cell that is taller than it should be, or a LEN that is higher than the characters you see. It is one step of the import cleanup, next to SUBSTITUTE for the non-breaking space and TRIM for ordinary spaces. If spaces are the only problem, TRIM alone is enough.

How Excel evaluates it

Excel reads the text and walks through it character by character. For each one it checks the character code: if the code is between 0 and 31, the character is dropped; otherwise it is copied.

CLEAN leaves these alone:

spaces (code 32), the first code that is kept
the non-breaking space (160)
the handful of other non-printing codes from 127 up (127, 129, 141, 143, 144, 157)

The last two need SUBSTITUTE. The result is text, and the source cell is not changed.

Watch out: CLEAN deletes a line break instead of replacing it, so the words on both sides are glued together. Turn the break into a space first when you want to keep them apart.

Mental model

A sieve with holes numbered 0 to 31: those characters fall through, everything else stays on top, including the spaces.

Terms used on this page: cell

Syntax

Official CLEAN documentation

CLEAN(text)

CLEAN(text)

Arguments
Argument	Type	Meaning
`text`	Required	The text to strip of control characters; a cell reference or text in quotes.

Return value:Text: the text with nonprintable characters removed

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Strip line breaks from entry numbers in a ledger export

You exported the journal lines of Veldhuis Installatie BV from the auditfile and want to match them with the entry numbers in the subledger.

The export puts an invisible line break at the end of most entry numbers. They look normal, but the cells are slightly taller and a lookup against the subledger returns #N/A. You add a column of cleaned entry numbers.

Sheet excerpt: C2 holds the formula
Row	A	B	C	D
1	Entry no. from export	Debit	Clean entry no.	Length
2	25-0412	€12,100.00€ 12.100,00	25-0412=CLEAN(A2)=CLEAN(A2)
3	25-0413	€3,025.00€ 3.025,00
4	25-0414	€450.00€ 450,00
5	25-0415	€9,680.00€ 9.680,00
6	25-0416	€1,815.50€ 1.815,50
7

Formula cell (C2)InputMarks: line feedNote: Every entry number except A4 ends with a line feed, CHAR(10). A4 was keyed in by hand and is 7 characters; the others are 8.

Formula in C2

=CLEAN(A2)

=CLEAN(A2)

Result: 25-0412

Step by step

Excel reads A2 and finds 25-0412 followed by a line feed: 8 characters. =LEN(A2) in D2 shows 8, one more than the 7 you can see.
It checks each character's code. The digits are 50, 53, 48, 52, 49, 50 and the hyphen is 45: all above 31, all kept.
The eighth character is the line feed, code 10, which is inside 0 to 31. It is dropped.
C2 shows 25-0412, 7 characters. Change D2 to =LEN(C2) and it shows 7.
Copy C2 down. A4 had no line feed and comes back unchanged; the other three lose their eighth character. All five entry numbers now match an exact lookup in the subledger.

Watch out: If the export used a Windows line ending, the entry number ends in two characters, CHAR(13) and CHAR(10). Both are below 32, so CLEAN removes both; LEN would have shown 9, not 8.

More on this example: why it works, 1 variation

Why it works

A line feed is a control character, code 10, and CLEAN drops every code from 0 to 31 without needing to be told which one is present. Because it copies everything else, an entry number that was already clean passes through unchanged, so the formula is safe to apply to the whole column.

Variations

=CLEAN(A2:A6)

=CLEAN(A2:A6)

In Excel 2021 or Microsoft 365 one formula in C2 spills all five clean entry numbers.

Intermediate

Clean and trim supplier names from a PDF

You copied the supplier names out of the client's payables specification (a PDF) to match them with the relations in the general ledger. Tabs sit where the columns were, with spaces around them.

One name still refuses to match after CLEAN, because it also holds a non-breaking space. You combine CLEAN with TRIM, and the walkthrough shows where that pair stops being enough.

Sheet excerpt: C4 holds the formula
Row	A	B	C	D
1	Supplier from PDF	Relation	Clean name	Length
2	Hofstede Autoverhuur BV	C2001
3	Bakker Kantoorinrichting BV	C2002
4	Yilmaz Transport BV	C2003	Yilmaz Transport BV=TRIM(CLEAN(A4))=TRIM(CLEAN(A4))
5	RamanSoftware BV	C2004
6	Visser Groothandel BV	C2005
7

Formula cell (C4)InputMarks: extra space, non-breaking space, tabNote: A2 starts with a tab and ends with a space. A3 ends with a tab. A4 starts with a tab and a space and ends with a tab. A5 has a non-breaking space (code 160) between Raman and Software instead of a normal space. A6 is clean.

Formula in C4

=TRIM(CLEAN(A4))

=TRIM(CLEAN(A4))

Result: Yilmaz Transport BV

Step by step

Excel evaluates CLEAN first. A4 is a tab, a space, Yilmaz Transport BV, a tab: 22 characters. The two tabs are code 9 and are dropped. The space is code 32 and stays. CLEAN returns a space followed by Yilmaz Transport BV, 20 characters.
TRIM receives that and removes the leading space: Yilmaz Transport BV, 19 characters. That is C4.
Without CLEAN, TRIM alone would leave both tabs in place, because a tab is not a space, and it would also keep the space after the first tab, because that space now sits between two non-space characters. The result would be all 22 characters and would still fail to match the ledger.
Copy C4 to the other rows. A2 (25 characters) loses its tab and its trailing space and ends at 23, A3 (28) loses its tab and ends at 27, A6 is unchanged at 21. All three end up at the visible length.
A5 is the exception. Raman, CHAR(160), Software, a space, BV is 17 characters. CLEAN keeps the CHAR(160) because 160 is above 31, TRIM keeps it because it is not code 32, and C5 still shows 17 characters that look like Raman Software BV but will not match it.

Watch out: CLEAN(TRIM(A4)), the other way round, gives a space followed by Yilmaz Transport BV: TRIM sees a tab at the start, not a space, leaves the space after it alone, and CLEAN removes the tab afterwards. CLEAN goes inside.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

CLEAN and TRIM cover two disjoint sets of characters: codes 0 to 31 and code 32. Running CLEAN on the inside means the tabs are gone before TRIM looks at the edges, so a tab that was hiding a space no longer protects it. The one character neither function covers, code 160, is why the Advanced example adds SUBSTITUTE.

More to watch out for

Put =CODE(MID(A5, 6, 1)) in a spare cell to see 160 for the character between Raman and Software; that is the quickest way to confirm a non-breaking space.

Variations

=TRIM(SUBSTITUTE(CLEAN(A4), CHAR(160), " "))

=TRIM(SUBSTITUTE(CLEAN(A4); CHAR(160); " "))

The complete trio, which also fixes A5. Use this as your default for anything pasted from outside Excel.

Advanced

Count which invoice numbers still hold hidden characters

Before a duplicate invoice test on a 5,0005.000-row subledger export, you want a single number: how many invoice numbers still contain a control character or a non-breaking space after the visible cleanup.

A hidden character makes two identical invoice numbers look different, so a duplicate stays undetected; a count of 0 is the go signal. You compare the length of each value with the length after the full cleaning trio, without a helper column.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Invoice no.	Amount	Rows to fix
2	F-2026-0120	€250.00€ 250,00	33=SUMPRODUCT(--(LEN(A2:A7) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2:A7), CHAR(160), " ")))))=SUMPRODUCT(--(LEN(A2:A7) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2:A7); CHAR(160); " ")))))
3	F-2026-0121	€99.50€ 99,50
4	F-2026-0122	€1,200.00€ 1.200,00
5	F-2026-0123	€45.00€ 45,00
6	F-2026-0124	€780.00€ 780,00
7	F-2026-0125	€12.75€ 12,75
8

Formula cell (D2)InputMarks: non-breaking space, tab, line feed, carriage returnNote: A3 ends with a carriage return and a line feed (codes 13 and 10), A4 ends with a non-breaking space (code 160), and A6 starts with a tab (code 9). The other three are clean. All six are 11 visible characters.

Formula in D2

=SUMPRODUCT(--(LEN(A2:A7) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2:A7), CHAR(160), " ")))))

=SUMPRODUCT(--(LEN(A2:A7) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2:A7); CHAR(160); " ")))))

Result: 33

Step by step

Excel evaluates the inner functions on the whole range at once, producing arrays. CLEAN(A2:A7) removes codes 0 to 31: A3 loses its two line-ending characters and A6 loses its tab. The other four are unchanged.
SUBSTITUTE replaces every CHAR(160) with a normal space: only A4 is affected, and it now ends with a normal space.
TRIM removes that space from A4. The cleaned array is six invoice numbers of 11 characters each, so LEN of it is {11;11;11;11;11;11}.
LEN of the original range is {11;13;12;11;12;11}: A3 had 13 characters, A4 and A6 had 12.
The <> comparison gives {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}; the double minus turns that into {0;1;1;0;1;0}.
SUMPRODUCT adds them: 0 + 1 + 1 + 0 + 1 + 0 = 3. D2 shows 3: three invoice numbers to fix before the duplicate test can be trusted.
Fix A3 by retyping F-2026-0121 and the count drops to 2. When it reads 0, every invoice number is exactly as long as it looks.

Watch out: A row with an ordinary trailing space also counts, because TRIM removes it and the length changes. That is usually what you want, but if spaces do not matter for your test, drop the TRIM and the count silently becomes a count of control characters and CHAR(160) only.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

A hidden character is invisible in the cell but not to LEN. The cleaning trio removes everything that would make an identical invoice number compare as different, so any row whose length changes is a row that could hide a duplicate. Doing it inside SUMPRODUCT keeps the sheet free of helper columns and gives one number that your audit program can point at.

More to watch out for

Characters above 255, such as zero-width spaces from web copy, survive all three functions and are not counted. The count can read 0 while a duplicate stays hidden; if that happens, use UNICODE on the suspicious cell.

Variations

=SUMPRODUCT(--(LEN(A2:A7) <> LEN(CLEAN(A2:A7))))

=SUMPRODUCT(--(LEN(A2:A7) <> LEN(CLEAN(A2:A7))))

Control characters only: returns 2 here (A3 and A6), because the CHAR(160) in A4 is not in CLEAN's range.

=IF(LEN(A2) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2), CHAR(160), " "))), "Fix", "OK")

=IF(LEN(A2) <> LEN(TRIM(SUBSTITUTE(CLEAN(A2); CHAR(160); " "))); "Fix"; "OK")

The per-row flag, to copy down next to the data when you need to see which rows, not only how many.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Clean multi-line journal descriptions without gluing words together

The descriptions in the auditfile hold line breaks between sentences and tabs where the bookkeeper pressed Tab. For your working paper the description must go into a single-line field, readable, with one space between words.

CLEAN alone would delete the line breaks and turn 'Manual entry' and 'bonus' into 'Manual entrybonus'. Instead you work in three steps, and you name each stage with LET so the next reviewer can see why the order is what it is:

convert the breaks to spaces
clean what remains
trim the extra spaces

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	Entry no.	Description	Single-line description
2	25-0731	Accrualholiday paycorrected	Accrual holiday pay corrected=LET( note, B2, breaksToSpaces, SUBSTITUTE(SUBSTITUTE(note, CHAR(10), " "), CHAR(9), " "), noControl, CLEAN(breaksToSpaces), TRIM(noControl) )=LET( note; B2; breaksToSpaces; SUBSTITUTE(SUBSTITUTE(note; CHAR(10); " "); CHAR(9); " "); noControl; CLEAN(breaksToSpaces); TRIM(noControl) )
3	25-0732	Reversal of accrualper client request
4	25-0733	Rent December
5	25-0734	Manual entrybonus Q4
6

Formula cell (D2)InputMarks: tab, line feed, carriage returnNote: B2 has a line feed after Accrual and a tab after holiday pay. B3 has a carriage return plus line feed after accrual. B5 has two line feeds, one in the middle and one at the very end.

Formula in D2

=LET(
  note, B2,
  breaksToSpaces, SUBSTITUTE(SUBSTITUTE(note, CHAR(10), " "), CHAR(9), " "),
  noControl, CLEAN(breaksToSpaces),
  TRIM(noControl)
)

=LET(
  note; B2;
  breaksToSpaces; SUBSTITUTE(SUBSTITUTE(note; CHAR(10); " "); CHAR(9); " ");
  noControl; CLEAN(breaksToSpaces);
  TRIM(noControl)
)

Result: Accrual holiday pay corrected

Step by step

note is B2: Accrual, a line feed, holiday pay, a tab, corrected. That is 29 characters, of which two are control characters.
breaksToSpaces runs two SUBSTITUTEs, inside out. The inner one replaces every CHAR(10) with a space: Accrual holiday pay, tab, corrected. The outer one replaces every CHAR(9) with a space: Accrual holiday pay corrected. Still 29 characters; two control characters became two spaces.
noControl is CLEAN of that. There are no control characters left in B2's text, so nothing changes here. For B3 this is the step that removes the CHAR(13), which the SUBSTITUTEs did not touch.
TRIM removes nothing here either, because there is exactly one space between each word and none at the edges. D2 shows Accrual holiday pay corrected.
Copy D2 to D3. B3: the CHAR(10) becomes a space, giving Reversal of accrual, CHAR(13), space, per client request. CLEAN deletes the CHAR(13). TRIM sees single spaces and changes nothing: Reversal of accrual per client request.
D5: both CHAR(10)s become spaces, giving Manual entry bonus Q4 with a trailing space. CLEAN has nothing to do; TRIM removes the trailing space. Without the SUBSTITUTE step, CLEAN would have produced Manual entrybonus Q4.

Watch out: The mirror image, CLEAN first and SUBSTITUTE after, looks equivalent and is not: by the time SUBSTITUTE runs, CLEAN has already deleted the CHAR(10) and there is nothing left to replace. Words are glued together and no error appears.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

CLEAN deletes; it never replaces. A line break between two words disappears without leaving a space, and that is the difference between a readable description and a merged one. So the pipeline runs in this order:

the breaks that carry meaning, CHAR(10) and CHAR(9), are converted to spaces first
CLEAN removes whatever remains (the CHAR(13) of Windows line endings, form feeds, anything else below 32)
TRIM collapses the doubled spaces that the conversion can create

LET makes the three stages visible in order.

Do not use this construction for key fields such as entry numbers and invoice numbers: there a line break is noise, not a word boundary, and TRIM(CLEAN(x)) is shorter and right.

The alternative for a recurring export is Power Query with a Replace Values step for the line feed followed by Clean and Trim. It does the same, remembers the recipe, and refreshes on demand rather than live.

More to watch out for

A description that ends with a sentence and then a line break gets a trailing space after the conversion; TRIM takes care of it, which is one reason TRIM is last. Leave TRIM out and the target field may be rejected for a trailing space.
If the descriptions also carry non-breaking spaces, add SUBSTITUTE(..., CHAR(160), " ") as a third conversion before CLEAN, as the TRIM page shows.

Variations

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(B2, CHAR(10), " "), CHAR(9), " ")))

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(B2; CHAR(10); " "); CHAR(9); " ")))

The same pipeline without LET, for a helper column. Read it inside out.

=TRIM(CLEAN(SUBSTITUTE(B2, CHAR(10), "; ")))

=TRIM(CLEAN(SUBSTITUTE(B2; CHAR(10); "; ")))

Keep the sentence boundary visible by converting each line break to a semicolon and a space. Tabs are then deleted by CLEAN, which is fine when they only ever sit at the ends.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Spaces at the end are still there	You used CLEAN on a pasted address and the spaces at the end are still there.	A space is code 32, one above CLEAN's range. TRIM removes spaces; CLEAN does not. Use TRIM(CLEAN(A2)) for both.
Words glued together after cleaning	After CLEAN, a multi-line address became one long word: Keizersgracht 12Amsterdam.	Convert the line break first, to a space or a comma: SUBSTITUTE(A2, CHAR(10), ", "), then CLEAN the rest.
LEN still one too high, lookup still fails	LEN is still one too high after CLEAN and TRIM, and the lookup still fails.	That is almost always CHAR(160), the non-breaking space from web pages. Add SUBSTITUTE(..., CHAR(160), " ") between CLEAN and TRIM. Use CODE on the last character to confirm.