Home » Remove Special Characters (@&}~#%$) in Excel

Remove Special Characters (@&}~#%$) in Excel

In this tutorial, I am going to explain how to remove special characters @&}~#%$ from a text string.  This method uses Power Query:  if you are using Excel 2013 you will need to download and install the Power Query add-in, using this link https://www.microsoft.com/en-gb/download/details.aspx?id=39379

If you have a more recent version of Excel (2016 or newer), the Power Query add-in is pre-installed.

Here we will assume you are applying this method to a column of values.  Follow these instructions to delete the special characters from all text strings in that column:

  1. Select one of the cells in the column containing the special characters.
  2. Select the Data tab on Excel’s ribbon.
  3. In the Get & Transform Data group, click the From Table/Range button.
  4. The Create Table dialog opens.  The data range should already be defined, if not select the cells you want to remove special characters from.  If you already have a column heading, make sure the ‘My table had headers‘ checkbox is checked.
  5. Click on OK to confirm.
  6. Power Query is launched in a separate window, displaying your data.
  7. Select the Add Column tab on Power Query’s ribbon.
  8. Click the Custom Column button (second from the left).
  9. In the Custom Column dialog, give your new column a name eg. ‘Data Without Special Characters’.
  10. In the Custom column formula box, start a formula with: =Text.Select(
  11. In the Available columns box, double-click the column name containing the text strings with special characters. This will enter that column name in your formula, which should now look like this =Text.Select([Your Column Name]
  12. Type a comma after the column name.
  13. After the comma type: {“A”..”z”,”0″..”9″})
  14. Your formula should now read as: =Text.Select([Your Column Name],{“A”..”z”,”0″..”9″})  Make sure you use a capital A and a lower-case z to extract both upper and lower-case characters.
  15. Click on OK to confirm.  The formula extracts characters A to Z and 0 to 9, leaving out special characters.
  16. To load from Power Query back into Excel, select File and then Close & Load.
  17. The data is loaded into a new worksheet.