Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

In this post I'm going to use the Text.Remove and Text.Select functions in PQ to extract characters from text strings.

I'll show you how to extract letters, either uppercase or lowercase, and a mixture of both, and how to extract numbers, and I'll show you a really cool way to remove a wide range of characters from strings.

I'm going to do this in Excel but you can use the same code in Power BI - just copy/paste the query code from the example file.

Watch the Video

 

Download Workbook

Starting with this table in Excel I've got a bunch of random text strings

So first things first, open Power Query by clicking into the table, then from the Data section of the Ribbon click on From table/range.

Rename the query to Text_Select - you can't use a dot in the name so can't call it Text.Select

Extract Lower Case Letters

To extract all the lower case letters, add a new custom column

Call the column Lower Case

The code is Text.Select ( [Text], {"a".."z"} ) , the .. means create a list from the first char "a" to the last "z", and include all letters in between

The contents of the new column look like this

OK that's pretty easy. You can probably guess how to extract the upper case letters.

Extract Upper Case Letters

Create another custom column and call it Upper Case. The code is

Text.Select ( [Text], {"A".."Z"} )

Extract Numbers

Extracting numbers follows the same pattern, create a custom column, call it Numbers, and the code is

Text.Select ( [Text], {"0".."9"} )

Extract Letters and Numbers

Extracting letters and numbers is a mix of the previous bits of code

We've already seen that to extract lower case letters we use a list define like this { "a".."z" }

So to also extract upper case letters and numbers you add the list definition used for each to give this

{ "a".."z", "A".."Z", "0".."9" }

Add a new custom column called Letters and Numbers, entering this code

and the result this gives is

That's it for Text.Select let's move on to Text.Remove

Text.Remove

As its name suggests, Text.Remove removes text from a string. The syntax is similar to Text.Select, you supply the text string then a list of characters to remove.

Text.Remove requires you to specify what to remove. Whereas Text.Select only needs you to tell it what to keep.

That sounds obvious to say but sometimes you my not know exactly what characters will be in the string so Text.Remove can be less flexible to use - but there is a way around this which I'll show you.

Let's duplicate the first query and rename it Text_Remove.

and Delete all these steps from here

Remove Lower Case Letters

If you have a relatively simple string then using Text.Remove can be quite easy.

To remove lower case letters, create a new column and unsuprisingly the code is this

Text.Remove( [Text], { "a".."z" } )

Remove Upper Case Letters

To remove upper case letters add a custom column and enter this code

Text.Remove( [Text], {"A".."Z"} )

Remove Numbers

Add another custom column and use this code

Text.Remove( [Text], {"0".."9"} )

Remove Letters and Numbers

If you had some reason to remove al the letters and numbers from a string then this is the code to do that (add a custom column first)

Text.Remove( [Text], {"a".."z", "A".."Z", "0".."9"} )

The Drawback of Text.Remove

All of these examples are straightforward but the drawback with Text.Remove is that using it this way you have to know what's in the string to remove it and this isn't always the case in the real world.

If you look at the text in row 5 of the Text column of my dummy data, it contains brackets/parentheses and lots of maths operators.

You could use this code to remove those characters, specifying each one individually

Text.Remove( [Text], { "(", "*", "+", "-", "/", ")" } )

But what if the string was even worse with lots of other characters, and more importantly, you didn't know what those characters were going to be?

Well here's where knowing what's going on under the hood of PQ helps.

It might seem obvious that when I create a list A..Z that I mean all the upper case letters from A to Z but what's actually happening in Power Query?

When you use the .. to create a list of sequential characters like A to Z what PQ is actually doing is using the character's Unicode value to construct the list.

Unicode is a technical standard for the consistent encoding, representation, and handling of text.

Looking at this Unicode character table you can see that A is represented by the decimal number 65 and Z is 90.

If you used the Character.ToNumber function it will return 65 for A and 90 for Z.

So the list specified by A..Z contains the characters specified by this Unicode table with decimal value 65 through to and including 90.

The key here is that the first character I specify, A, has a lower Unicode value than Z.

I can actually create a list with any characters as long as the starting char has a lower Unicode value than the ending character.

I'll show you another example.

If I want to keep upper case letters in my string then I want to remove everything else.

Rather than creating one list for lower case letters and then another list for numbers and maybe several other separate chars that all need to be removed, using the Unicode table I can create a list that specifies every character to remove.

Starting with the space char " " (Decimal 32)

Looking down the table you can see that the last char before uppercase A is the @ so I can write " ".."@" and that means create a list of every character between space and @.

After Z is left square bracket and then the last character in this table is the tilde.

I can write my list of characters to remove as

Text.Remove( [Text], {" ".."@", "[".."~"} )

If I want to clean up the mess in row 5 and be left with just numbers my code would start creating a list with space again being the first character.

Checking the Unicode table, I want to go down to the slash (decimal 47)

Skip over the numbers, and start again with the first character after 9 which is the colon

Then go to the end which is the tilde character.

The code to remove everything except numbers is

Text.Remove([Text], {" ".."/", ":".."~"} )

Summary

So that's how to use Text.Select and Text.Remove.

I should say that I'm working with standard English Unicode set. You may be using an extended character set depending on your language and/or locale.

Best of luck cleaning up those messy strings.