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
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.