Text.Remove
Removes every occurrence of the listed characters from a text value.
Text.Remove(text as nullable text, removeChars as any) as nullable textReturns: The text with those characters removed, or null when the input is null.
What it does
Text.Remove deletes a set of characters wherever they appear. It is the fastest way to strip punctuation out of an amount or a code. You give it a list of characters, and every one of them is taken out, not only the first.
Syntax and arguments
- text: the value to clean.
- removeChars: a list of single characters, such as {"(", ")", ","}, or a single character as text.
Example
A bank export shows a negative amount as "(1,234.50)". The team removes the parentheses and the commas, then makes the number negative when the original had brackets.
let
Raw = "(1,234.50)",
Digits = Text.Remove(Raw, {"(", ")", ","}),
Value = Number.FromText(Digits, "en-US"),
Signed = if Text.StartsWith(Raw, "(") then -Value else Value
in
Signed
Digits is "1234.50" and the result is -1234.5. The brackets tell the bookkeeper the amount is negative, and the code keeps that rule explicit.
Example: a second, different use
Remove the dashes and spaces from a bank routing number so it can be checked for nine digits with Text.Length.
Things to watch
- It removes every match of every character in the list. Removing "-" from "1000-Cash" also removes any other dash in the name.
- Text.Remove works on single characters. For a whole word, use Text.Replace.
- The removal is case sensitive for letters.
- Null in gives null out.
In business terms
Bank exports often show negatives as brackets and amounts with thousands separators. Cleaning them with Text.Remove and converting with Number.FromText is how a bank file becomes a column that sums and signs correctly.