Hidden character

[Giordano Bruno]

I've inherited a workbook with a bullseye symbol in cell A1. The curious thing is that the formula bar shows nothing in the cell other than a wide space.
When I check it with the formula in B1 =CODE(A1), it returns 158. When I enter the formula =CHAR(B1) it returns the character "ž". If I format this character as Windings 2, I get a bullseye symbol, but this time the character ž is visible in the formula bar.
Does anyone know how the entry in A1 is hidden. Note that no protection has been invoked.

 

[Fluff]

Conditional formatting perhaps?

 

[Giordano Bruno]

Hi Fluff. Thanks for the thought, but the formula bar looks empty. The cell displays the symbol and its colour and size can be changed in the normal way. If I press F2 to edit the character, I can left arrow to make the cursor pass it, but I cannot see anything.

 

[pgc01]

Hi Giordano

CODE() is not a good way to see what character is in the cell.

What do you get when you use

=UNICODE(A1)

or, if you have and old version of excel, execute in vba:

MsgBox AscW(Range("A1").Value)

 

[Giordano Bruno]

Hi pgc01. The Unicode formula also returns 154 as I believe is should since it's less than 256. However, UNICHAR gives a different value for 154, i.e. a wide space rather than the character "ž". The only other slight difference is that CHAR results in the symbol being centred whereas UNICHAR leaves the character slightly to the right of centre.
I have found that I can use UNICHAR to recreate the effect. Format a cell font as Windings 2. Type =UNICHAR(158) into a cell. press F2, F9 Enter and you have the character with an invisible value.
Do you have an explanation for this?

 

[pgc01]

Hi

Well, I can give you my take on this.

If you remember, ascii is a 7 bit code, and so goes from 0 to 127, I remember using it with communications processors in the old times (giving good use to the some of the control characters before the code 32).
With the computers, since the basic unit was the byte (8 bits - 0 to 255), that gave you the opportunity to define those extra codes (128 to 255) to accommodate for the region specific characters.
Code pages were defined for regions or alphabets (Western Europe, Central Europe, Cyrillic, Greek, Vietnamese, etc.)

Now getting back to your character, the 158 is one of Unicode control characters (128 to 159), in this case the character 158 is the control character: 'PRIVACY MESSAGE'.
Since it's a control character it does not need to be displayed, and so you get your space.
You can however, display its equivalent character in the code page of your region.
In my case, my system uses the code page 1252, for Western European languages, which gives me the character ž, like you.
I guess that if I was in Russia my system would use the code page 1251, for Cyrillic, and I'd get the character ћ.

You can check these code pages

Western Europe (code page 1252): https://en.wikipedia.org/wiki/Windows-1252
Cyrillic (code page 1251): https://en.wikipedia.org/wiki/Windows-1251

In conclusion: the Unicode character 158 is a control character and so it does not have a visible display.
Your system may display the equivalent ANSI equivalent from your system regional definitions, in your case the ž, but could be another character in your computer had other regional settings.

Remark:
The equivalent ANSI characters that are displayed, and that can vary, also exist as UNICODE characters

ž - character 382 - Latin Small Letter Z With Caron
ћ - character 1115 - Cyrillic Small Letter Tshe

Hope it helps.

 

[Giordano Bruno]

Hi pgc01. Thanks so much for that excellent explanation and for taking the trouble to explain it so clearly and thoroughly. I really appreciate the detailed background information. I've learned a lot.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.