Text.Clean
Removes non-printing control characters, such as line breaks and tabs, from a text value.
Text.Clean(text as nullable text) as nullable textReturns: The text without control characters, or null when the input is null.
What it does
Memo and description fields often contain line breaks or tabs that came in from a copied PDF, an email or a web page. They look like ordinary text, but they break a report layout and stop matches. Text.Clean removes the non-printing control characters from the value. It does not remove normal spaces or visible punctuation, so pair it with Text.Trim when you also need the spacing tidy.
Syntax and arguments
- text: the value to clean. This is the only argument.
Example
A controller copies transaction memos out of a PDF bank statement. Some memos wrap onto two lines, which breaks the report. The memo column is cleaned, then trimmed.
let
Memo = "Payment received" & "#(lf)" & "Invoice 1043",
Cleaned = Text.Trim(Text.Clean(Memo))
in
Cleaned
The line break is removed, not turned into a space, so the result is "Payment receivedInvoice 1043". The two words run together. Replace the break with a space first (Text.Replace) when the words must stay apart.
Example: a second, different use
Before a merge, clean an invoice description that contains a tab character copied from a spreadsheet.
Things to watch
- Text.Clean removes the line break without adding a space. Use Text.Replace to swap line breaks for a space when the words must stay apart.
- It does not remove ordinary spaces. Add Text.Trim for those.
- Characters outside the printable range are removed, but accented letters and currency symbols stay.
- Null in gives null out.
In business terms
Memo and description fields are where bank and vendor data arrive messiest. Cleaning them before a report or a reconciliation stops a long memo from breaking a column, and stops a match from failing on an invisible character.