Text.Select
Keeps only the listed characters from a text value and removes the rest.
Text.Select(text as nullable text, selectChars as any) as nullable textReturns: The text made of only the selected characters, or null when the input is null.
What it does
Text.Select is the opposite of Text.Remove. It keeps the characters you name and drops everything else. A common use is pulling the digits out of a messy reference, such as "INV-2024/0042 rev" becoming "20240042", so the number can be compared or sorted.
Syntax and arguments
- text: the value to clean.
- selectChars: a list of the characters to keep, or a single character as text. A range such as {"0".."9"} lists every digit.
Example
Vendor invoice numbers arrive with letters, dashes and slashes. The team keeps only the digits so invoices from different vendors can be sorted by number.
let
Invoices = #table(type table [Invoice = text],
{{"INV-2024/0042"}, {"Bill No. 1043 rev"}, {"SI 88341"}}),
Digits = Table.AddColumn(Invoices, "Digits",
each Text.Select([Invoice], {"0".."9"}), type text)
in
Digits
The digits column holds "20240042", "1043" and "88341". The letters, spaces and punctuation are gone. Keep the original invoice column for the reader, since the digits alone can be ambiguous.
Example: a second, different use
Keep only the letters in a vendor code to get the alphabetic prefix, such as "ACME" from "ACME-0042", for grouping by vendor family.
Things to watch
- A digit-only result can merge two different references, such as "INV-12" and "INV 12", so do not use it as a unique key without checking.
- The result is text. Convert with Number.FromText if you need arithmetic or numeric sorting.
- Text.Select keeps characters in the order they appear.
- Null in gives null out.
In business terms
Invoice, check and reference numbers rarely come in one clean format. Pulling out the digits gives a comparable number for sorting and matching, as long as the team confirms the rule on a sample first.