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,
Comments