UDF to remove special characters, punctuation & spaces within a cell to create unique key for Vlookups

user2993456 picture user2993456 · Mar 6, 2014 · Viewed 11.9k times · Source

I hacked together the following User Defined Function in VBA that allows me to remove certain non-text characters from any given Cell.

The code is as follows:

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?™""®<>|.&@#(_+`©~);-+=^$!,'" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

This portion of the code obviously defines what characters are to be removed:

sSpecialChars = "\/:*?™""®<>|.&@#(_+`©~);-+=^$!,'"

I also want to include a normal space character, " ", within this criteria. I was wondering if there is some sort of escape character that I can use to do this?

So, my goal is to be able to run this function, and have it remove all specified characters from a given Excel Cell, while also removing all spaces.

Also, I realize I could do this with a =SUBSTITUTE function within Excel itself, but I would like to know if it is possible in VBA.

Edit: It's fixed! Thank you simoco!

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?™""®<>|.&@# (_+`©~);-+=^$!,'" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces
    Next
    removeSpecial = sInput
End Function

Answer

user2993456 picture user2993456 · Mar 15, 2014

So after the advice from simoco I was able to modify my for loop:

For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces
    Next

Now for every character in a given cell in my spreadsheet, the special characters are removed and replaced with nothing. This is essentially done by the Replace$ and Mid$ functions used together as shown:

sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces

This code is executed for every single character in the cell starting with the character at position 1, via my for loop.

Hopefully this answer benefits someone in the future if the stumble upon my original question.