Remove or Extract Special Characters Using Power Query

Power Query’s Text.Select keeps only the characters you list, and Text.Remove throws them away. With one custom column you can strip !@#$%&* from messy text, or pull out just the numbers, capitals or letters.

FunctionsText.Select, Text.Remove
Works inExcel 2016 and later, Microsoft 365
RefreshableYes

The data

Text strings containing special characters in Excel

Step 1: Load the data into Power Query

Click a cell in the data, go to Data → From Table/Range, and click OK in Create Table.

Create Table dialog
Data loaded into the Power Query Editor

Step 2: Add a custom column

Go to Add Column → Custom Column, name the column, and enter one of the formulas below.

Custom Column button on the Add Column tab

Remove special characters (keep letters, numbers and spaces)

Text.Select([Text String], {"A".."Z", "a".."z", "0".."9", " "})
Custom Column dialog with Text.Select formula
Clean Data column without special characters

Why not {"A".."z"}? A range runs through every character in between, and between Z and a sit [ \ ] ^ _ “. List capitals and small letters separately so those don’t slip through.

Extract only the special characters

Text.Remove([Text String], {"A".."Z", "a".."z", "0".."9", " "})
Custom Column with Text.Remove formula
Column showing only the special characters

More patterns

To keep Formula
Numbers only Text.Select([Text String], {"0".."9"})
Capital letters only Text.Select([Text String], {"A".."Z"})
Small letters only Text.Select([Text String], {"a".."z"})
All English letters Text.Select([Text String], {"A".."Z", "a".."z"})
Only Numbers column in Power Query
Capital Letters column in Power Query
Small Letters column in Power Query
English Alphabets column in Power Query

Step 3: Load and refresh

Remove any columns you don’t need (right-click the header → Remove), then Home → Close & Load To → Existing worksheet.

Import Data dialog
Cleaned data loaded back into Excel

When new rows are added to the source, right-click the result and choose Refresh.

Refreshing the Power Query output after adding data

Excel 365: a formula alternative

In current Microsoft 365, REGEXREPLACE does the same in a cell:

=REGEXREPLACE(A2,"[^A-Za-z0-9 ]","")

[^...] means anything not in this list, so every other character is replaced with nothing.

Things to know

  • Text.Select and Text.Remove are case-sensitive: "A".."Z" doesn’t include small letters.
  • Accented letters (é, ñ) and Indian scripts are outside A..Z, so they are treated as special characters. Add them to the list if you need them.
  • Power Query formulas are written without the leading = in the Custom Column box; Excel adds it for you.

Related

Last updated · Written by Ajay Anand, Microsoft Excel MVP