We need to identify the miscreant character first and then remove it.
First put the following User Defined Function in a standard module:
Public Function WhatsIn(s As String) As String
Dim msg As String, i As Long, L As Long
L = Len(s)
msg = L & vbCrLf
For i = 1 To L
msg = msg & i & " " & AscW(Mid(s, i, 1)) & vbCrLf
Next i
WhatsIn = msg
End Function
The function tells us exactly what a cell contains. It gives:
- the number of characters
- for each character, the unicode value
Here is an example:
![enter image description here](../../I/static/images/88c169483cc1fdc60cad0fc826ac0a85603cf98b6b8c5bae0bb8623ea14bf968.png)
We know how many characters cell C2 contains and we know the ASCII code for each character.
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module
- paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=WhatsIn(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
Once we know the ASCII number, run a macro like:
Sub KleanUp()
Cells.Replace ChrW(166), ""
End Sub
where we would replace the 166 with the actual number.
Can you isolate the "junk" character in a cell by removing any good ones ?? – Gary's Student – 2018-01-04T20:24:33.120
I isolated that character and searched the Excel file for it, but it turned up 75,000 blank cells., and not the problem cell in question. – Adam White – 2018-01-04T20:34:50.800
The two brackets (like ‘[]’) are Excel’s way to display a character that it can’t display in the current font. So it really doesn’t give much info beyond ‘a strange character’. – Aganju – 2018-01-05T03:22:48.750