I’ve been using Chip Pearson’s Cell View for years. It does just one major thing, but it does it extremely well: It shows you the ascii or hex codes for every character in a cell, and if there are hidden or trailing (or leading) characters, it flags them. A common example would be the non-breaking space, ascii(160). If you’re wondering why your FIND(” “,A1) isn’t working, Cell View will show you if it’s ascii(32) or ascii(160) that you have at hand. Here’s Chip’s example with tab character ascii(9) in the middle. Note the red caret indicating the special character.
If ascii isn’t what you want, you can optionally display the codes in hexadecimal. It does this magic by adding a “View Cell Contents” item at the bottom of the View Menu, just where you’d want it to be. Chip gives this utility away, and even provides the password with an invitation to change it. With the temerity gathered from hanging around here, I altered some minor things to suit my preferences a bit more.
The first two of these changes are in the frmShowChars code (right-click on the form frmShowChars and View Code). Chip allows you to select a starting position by counting by one’s. I made a mod to make it count by ten’s. Change the With statement in Sub UserForm_Activate() as so:
Dim N As Long
With Me.cbxStart
.Clear
.AddItem Format(1, “##0”) ‘Added
For N = 10 To Len(ActiveCell.Text) Step 10 ‘Changed
.AddItem Format(N, “##0”)
Next N
If .ListCount > 0 Then
.ListIndex = 0
End If
End With
Me.lblVarType.Caption = vbNullString
DoIt
End Sub
Counting by one’s seemed like overkill when coupled with a desire for the caption to tell me the cell’s total length. I changed the very top lines of code in Sub DoIt() as follows:
Me.lblCode.Caption = “Hex”
Else
Me.lblCode.Caption = “Ascii” ‘Changed
End If
N = VBA.Len(ActiveCell.Text) ‘Added
C = IIf(N <> 1, ” characters.”, ” character.”) ‘Added
Me.Caption = “Character Codes For Cell: “ & ActiveCell.Address(False, False) _
& “. This cell” & Chr(146) & “s length is “ & N & C ‘Added with underscore
With these changes, Cell View looks like this, showing special character acsii(160):
“View Cell Contents” is enabled even when no worksheet is visible or when no workbook is open. If you call it then, it throws an error. A fix was to edit the Sub ShowTheForm() found in the regular module modMain by calling Chip’s splash screen and then exiting:
If ActiveCell Is Nothing Then ‘Added
frmAbout.Show ‘Added
Exit Sub ‘ Added
End If ‘Added
#If VBA6 Then
frmShowChars.Show vbModeless
#Else
frmShowChars.Show
#End If
End Sub
For Mac users, this version of Cell View does not work. A previous version called HexChars.xla does. The bad news is that you need a PC to unlock the code. Maybe it’ll be fixed in Excel 2011. It’s on my birthday list.
Cell View is a .xla file (CellView.xla). Put it in your add-ins folder and load it via Tools/Add-Ins…
…mrt
Where to download this new version???
Hi Dan –
The link is in the first line at the top. If you want a version with the discussed mods in it, you’ll have to roll your own. Chip’s website gives the VBA password (the letter “a”, no quotes).
I should point out that the “count by tens” mod eliminates the full functionality of the delete character button, a feature I never use.
…mrt
The horizontal layout confuses me. I know that’s how people read, but isn’t this begging for a listbox and a scrollbar? That would completely eliminate the need for a “start at char” combo and users already get how to use a listbox.