If you’ve ever worked with text strings in Excel, you may have come across hidden characters that make it difficult to work with the data. These special characters can be a nuisance when you’re trying to manipulate the text, but fortunately, they can be removed with the help of Power Query.
Before we begin, it’s important to note that if you have Excel 2010 or 2013, you’ll need to install Power Query as an add-in. You can find a link to download and install the add-in on the Microsoft website. If you have Excel 2016 or Excel 365, Power Query is pre-installed.
Once you have Power Query installed, click into your data somewhere and go up to Data on your ribbon. Click on From Table/Range, and you’ll see a dialog box confirming the range of cells you’re working on. Be sure to check the “My table has headers” box if you already have a header in your data.
This will launch Power Query and show the data you had in your spreadsheet. To remove the special characters, we’re going to create an additional column using a function called text.select. Click on Add Column, then Custom Column. Name the column “Data without Special Characters.”
In the formula bar, type: = Text.Select([Data with Special Characters],{“A”..”Z”,”a”..”z”,”0″..”9″}).
This formula will extract all the characters you want while ignoring the special characters. Be sure to include the curly brackets and quotation marks around the characters you want to select.
Finally, go to File > Close and Load to put the cleaned data on another sheet.
With these simple steps, you can easily remove hidden characters in Excel and work with clean, formatted data. No more struggling with special characters and difficult-to-read text strings!