I would describe this as more modestly convenient than cool. It lists the text of a cell (the first 200 characters) in binary so you can see those annoying non-breaking spaces (160), carriage returns (013), tabs (009), and other characters that bedevil lookups and other functions. I keep this in my Personal.xls with a Ctrl+Shift+B (think Conveniently Show Binary) shortcut.
Code
Sub ShowBinary()
' shortcut: Ctrl+Shift+B
' Lists the binary contents (up to 200 characters)
' of the top left cell in the selected range
Const sTitle As String = "Binary listing of cell text: "
Dim sInp As String
Dim sOut As String
Dim i As Long
Dim sAdr As String
If TypeName(Selection) <> "Range" Then
MsgBox "Doesn't work for selections other than ranges!", _
vbInformation, sTitle & TypeName(Selection)
Exit Sub '---------------------------------------------------------->
End If
sInp = Selection(1, 1).Text
sAdr = Selection(1, 1).Address(False, False)
If Len(sInp) = 0 Then
MsgBox "Cell text is null", vbInformation, sTitle & sAdr
Exit Sub '---------------------------------------------------------->
End If
For i = 1 To Len(sInp)
If i Mod 10 = 1 Then
If i = 201 Then
sOut = sOut & vbLf & "..."
Exit For
Else
sOut = sOut & vbLf & Format(i, "000: ")
End If
End If
sOut = sOut & Format(Asc(Mid(sInp, i, 1)), " 000")
If i Mod 5 = 0 Then sOut = sOut & " "
Next i
MsgBox Mid(sOut, 2), vbInformation, sTitle & sAdr
End Sub
Display More