Remove special characters from an Excel 365 worksheet

Question

Remove special characters from an Excel 365 worksheet

Anonymous

Greetings,

We receive a downloaded file from an external source on a weekly basis. It contains special characters, like #, $, %, etc. I need to find a way to remove them all.

The size of the files, row and column varies. It does contain underscores and dashes and these need to be retained. The file name may change weekly, as it is received, but we certainly can rename it.

Is there a way to remove them in a single operation. I have done a series of Find / Replace such as Find #, Replace all with nothing. It works but can be time consuming.

Any suggestions will be greatly appreciated.

Paul

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

2 additional answers

Answer 1

You can adapt and run the following macro:

Sub RemoveChars()
    Dim v
    Application.ScreenUpdating = False
    ' Modify as needed
    For Each v In Array("#", "$", "%")
        Cells.Replace What:=v, Replacement:="", LookAt:=xlPart
    Next v
    Application.ScreenUpdating = True
End Sub

Add characters to be removed to the array.

If you store the macro in your Personal Macro Workbook PERSONAL.XLSB, you can run it whenever you need it.

See Excel Personal Macro Workbook | Save & Use Macros in All Workbooks if you need more information.

Answer 2

depends on the symbols.

Importing the data into PowerQuery automatically removed $ and converted % to matching percent value.

.

Then you can use Text.Remove (to delete specific char) or Text.Select function (to keep only listed characters, ie A-z and 0-9) to get rid of any other characters. Which ever function works better for you. https://www.youtube.com/watch?v=II4wvIifIQg Then the only remaining trick is to add code to handle variable number of columns. And I've seen that done with some relatively simple hand coding.

.

Answer 3

Re: "remove them in a single operation"

You don't mention numbers?

If all the data was in one column, you could do a massive =Substitute(Substitute(Substitute(Substitute(... formula.

-or-

VBA could do the find/replace for the entire sheet, if you provide it with a complete list of characters to replace.

'---
Nothing Left to Lose

Share via

Remove special characters from an Excel 365 worksheet

2 additional answers