VBA Code - Remove invisible special characters from string

Sometimes in excel data, we encounter various unwanted characters, spaces etc which are not cleaned by common clean function in the excel. The following function easily clear those characters.

Function sFnRemoveSC(sInput As String) As String
Dim lLoop As Long, sSpecialChars As String
sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`"
For lLoop = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, lLoop, 1), " ")
Next
sFnRemoveSC = sInput
End Function

If you by "Invisible" characters mean "not on the keyboard", then use the code number and add to the string using the character code 

sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`" + Chr(9) + Chr(10)
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
Dim X As Long, CodesToClean As Variant
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
For X = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
Next
CleanTrim = WorksheetFunction.Trim(S)
End Function

CLEAN removes characters 0-31, 129, 141, 143, 144, and 157, so you can simplify your formula to just remove the DEL and NBS characters: 

=TRIM( CLEAN( SUBSTITUTE( SUBSTITUTE(A1, CHAR(127), " "), CHAR(160), " ") ) )

Comments

Popular posts from this blog

WIPO Pearl - Recap

Zotero - Next Generation Research Tool