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

Question

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

`I also want to include a normal space character` - actually it's make no sense to me. If you include space, your `Replace$` function would replace _space_ with _space_ — Dmitry Pavliv, Mar 06 '14 at 21:03
Just use a space in the quoted text of your special characters. No escaping is needed. — RBarryYoung, Mar 06 '14 at 21:04
Hmm I have tried that, and my UDF still does not seem to remove space, I will post my updated code — , Mar 06 '14 at 21:06
@AdamJ, have you seen my comment above? Your function replaces space with space. And it's expected that nothing happend (because you have third parameter of `Replace$` equals to `" "` - space, just replace it with empty string `""`) — Dmitry Pavliv, Mar 06 '14 at 21:07
Oh I think I see what you mean, I totally missed that in the code. Silly me. I will correct and post back when fixed. Thank you for the help. — , Mar 06 '14 at 21:10

score 1 · Accepted Answer · answered Mar 14 '14 at 23:59

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.

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

1 Answers1