Text.Clean
TextRemoves all control characters from a text value.
Syntax
Text.Clean(text as nullable text) as nullable textParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | The text value to clean. |
Return Value
text — The input text with all control characters (characters with Unicode values 0–31 and 127) removed.
Remarks
Text.Clean removes non-printable control characters — the same characters Excel's CLEAN function removes. This includes carriage returns (#(cr)), line feeds (#(lf)), tabs (#(tab)), and other characters with Unicode code points 0–31 and 127.
Use Text.Clean to sanitize text imported from legacy systems, CSV files, or copy-pasted content that may contain invisible formatting characters. For stripping specific characters beyond control codes, use Text.Remove instead.
Text.Trim removes leading/trailing whitespace; Text.Clean removes embedded control characters anywhere in the string. Both are often applied together.
Examples
Example 1: Remove embedded line feeds and tabs from imported text
let
Messy = "Hello" & "#(lf)" & "World" & "#(tab)",
Cleaned = Text.Clean(Messy)
in
CleanedThe final output — removes all control characters (including the line feed and tab) from the messy string, returning only the printable characters concatenated together.
Result | |
|---|---|
| 1 | HelloWorld |
Example 2: Clean a column of messy imported values
Table.TransformColumns(Source, {{"Notes", Text.Clean, type text}})Compatibility
