Text.Select Function in Power Query M (5 Easy Examples)

Text.Select keeps only the characters you list from a text value and drops everything else. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to pull just the digits out of a messy string, or keep only letters and strip out spaces and symbols, this is the function you reach for.

Table of Contents

Syntax of Text.Select Function

Text.Select(text as nullable text, selectChars as any) as nullable text

where

text (required, nullable text). The string you want to filter. It can be null.
selectChars (required, any). The characters to keep. Pass a single character, or a list of characters. Lists can use the range shorthand "a".."z", "A".."Z", and "0".."9", and you can mix ranges with single characters in one list.

Returns: a text value containing only the characters from text that appear in selectChars, in their original order. If text is null, it returns null.

In plain terms, you hand it some text and the set of characters worth keeping, and it gives you back the text with everything else stripped out.

Example 1: Pull only the digits from a string

You have an order reference like Order#5821 and you want just the number.

Text.Select("Order#5821",{"0".."9"})

Result: 5821

The letters, the #, and everything that is not a digit get dropped.

Example 2: Keep only letters and numbers

Strip out spaces, punctuation, and symbols, but keep both letter cases and digits.

Text.Select("Wi-Fi: Cafe_42!",{"a".."z","A".."Z","0".."9"})

Result: WiFiCafe42

The hyphen, colon, space, underscore, and ! are removed. Because the list has both "a".."z" and "A".."Z", upper and lower case both survive.

Example 3: Keep digits and the decimal point

Numbers with a decimal point need that point in the list, or it gets stripped too.

Text.Select("Total: 3.75 USD",{"0".."9","."})

Result: 3.75

The . is in the list, so it stays. The result is still text, not a number.

Example 4: Clean a phone-number column to digits only

The most common real use is cleaning a whole column inside a query.

Say you have a Contacts query with a Name and a RawPhone column, where each phone number is formatted differently.

Here is the starting data:

Name	RawPhone
Priya	(415) 938-2270
Marco	+1 503.771.6644
Lena	tel: 628-4519 ext 7

Add a column that keeps only the digits from RawPhone:

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    Cleaned = Table.AddColumn(Source,"DigitsOnly",each Text.Select([RawPhone],{"0".."9"}),type text),
    Result = Table.SelectColumns(Cleaned,{"Name","DigitsOnly"})
in
    Result

The result has each number reduced to its digits:

Name	DigitsOnly
Priya	4159382270
Marco	15037716644
Lena	62845197

Every bracket, space, plus sign, dot, and word like tel or ext is gone, leaving a clean run of digits. The final step uses Table.SelectColumns to keep just the two columns we care about.

Example 5: An empty string returns an empty string

When the input has no characters at all, there is nothing to keep.

Text.Select("",{"a".."z"})

Result: ""

Feed it an empty string and you get an empty string back. Worth knowing because null behaves differently here. A null input returns null.

Things to keep in mind with Text.Select

It keeps, it does not remove. Text.Select keeps the characters you list and drops the rest. Text.Remove does the opposite and deletes the characters you list. For “keep only letters” reach for Text.Select; if you just need to clean whitespace off the ends, Text.Trim is the simpler choice.
selectChars is case-sensitive. {"a".."z"} keeps lowercase only, so "GoLang" becomes "oang". To keep both cases, list both ranges with {"a".."z","A".."Z"}.
Accented and non-ASCII letters fall outside the basic ranges. Characters like é, ñ, and ü are not inside "a".."z" or "A".."Z", so they get dropped. Text.Select("Café",{"a".."z","A".."Z"}) returns "Caf". Add them to the list explicitly if you need to keep them.
The result is text even when it looks like a number. Keeping {"0".."9","."} on a price still gives you text. Wrap the result in Number.FromText when you need a numeric type.
A non-text, non-null value errors. Passing a number raises Expression.Error: We cannot convert the value 5 to type Text. Convert the value first with Text.From([Column]) before piping it into Text.Select.

Common questions about Text.Select

Why does the list use the .. notation?

"a".."z" is M list-range shorthand that expands to every character from a to z. It is a set of characters to match against, not a regex pattern, so there is no wildcard or pattern matching, only a membership test. If you instead need to test whether a substring appears in the text, that is a job for Text.Contains.

How is this different from Text.Range or Text.Middle?

Text.Range and Text.Middle pull characters out by position, using a start index and a length. Text.Select pulls them out by identity, keeping whichever listed characters appear, no matter where they sit in the string.

List of All Power Query Functions

Related Power Query Functions / Articles: