Alright, I have created (with the best help from StackOverflow) a User Defined Function, which Identifies specific (invisible), non-printable control characters within a given cell. The UDF then creates a message box telling the user what character has been found and removed.
The functions does not bother to locate every one of the 32 ASCII control characters, it only fines the one which don't have a graphical representation in Excel.
Here is the function in it's current state:
Function findInvisChar(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
Dim sReplaced As String
Dim ln As Integer
sSpecialChars = "" & Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & Chr(32) 'This is your list of characters to be removed
'For loop will repeat equal to the length of the sSpecialChars string
'loop will check each character within sInput to see if it matches any character within the sSpecialChars string
For i = 1 To Len(sSpecialChars)
ln = Len(sInput) 'sets the integer variable 'ln' equal to the total length of the input for every iteration of the loop
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
'If ln <> Len(sInput) Then sReplaced = sReplaced & Mid$(sSpecialChars, i, 1)
If ln <> Len(sInput) Then sReplaced = sReplaced & IIf(Mid$(sSpecialChars, i, 1) = Chr(10), "<Line Feed>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(1), "<Start of Heading>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(9), "<Character Tabulation, Horizontal Tabulation>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(13), "<Carriage Return>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(28), "<File Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(29), "<Group separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(30), "<Record Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(31), "<Unit Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about invisible characters which were removed
Next
MsgBox sReplaced & "These were removed"
sInput = UCase(sInput)
findSpecial = sInput
End Function 'end of function
What I am trying to do, is to also make this function identify non-breaking space characters. These have a unicode value of U+00A0. This is the portion of code which I have created to identify a non-breaking space, if it in fact does appear in a target cell:
IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1))
I am having trouble with the ChrW() function. To my understanding it accepts the hexadecimal value of a unicode character, and in this case that value would be A0, if I am not mistaken.
I have placed a non-breaking space into an excel document to test the function, but when I do, the function does not work. The messaage box simply shows up blank, instead of telling the user that "...These characters were removed.
Am I using the ChrW() function incorrectly? Or is there another issue with my code or testing method that I may be missing?
I will answer my own question so that it does not linger without an answer.
The issue was that I had not previously told the function to find and remove the non-breaking space, in my string variable list of characters to be removed, sSpecialChars. I added ChrW(&HA0) to that string and now everything works swimmingly.
Thank you Portland Runner for showing me the proper way of using the ChrW() function.
For reference, my final code is as follows:
Function findInvisChar(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
Dim sReplaced As String
Dim ln As Integer
sSpecialChars = "" & Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & Chr(32) & ChrW(&HA0) 'This is your list of characters to be removed
'For loop will repeat equal to the length of the sSpecialChars string
'loop will check each character within sInput to see if it matches any character within the sSpecialChars string
For i = 1 To Len(sSpecialChars)
ln = Len(sInput) 'sets the integer variable 'ln' equal to the total length of the input for every iteration of the loop
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
If ln <> Len(sInput) Then sReplaced = sReplaced & Mid$(sSpecialChars, i, 1)
If ln <> Len(sInput) Then sReplaced = sReplaced & IIf(Mid$(sSpecialChars, i, 1) = Chr(10), "<Line Feed>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(1), "<Start of Heading>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(9), "<Character Tabulation, Horizontal Tabulation>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(13), "<Carriage Return>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(28), "<File Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(29), "<Group separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(30), "<Record Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(31), "<Unit Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = ChrW(&HA0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about Bell and Line Feed
Next
MsgBox sReplaced & " These were identified and removed"
findInvisChar = sInput
End Function 'end of function