In cell E1 it says You-Gov but in the formula bar it says YouGov. If I copy/paste values it stays the same, the formatting is General. Using =CODE(MID($E$1,4,1)) I get a value of 173. Any idea what is happening?

CodePudding user response：

What you have got here is called the "Soft Hyphen". It's purpose is to let the system know where a word may be broken, if needed, for display purposes.

"The HTML standard states that a hyphen character should be displayed at the end of the line where the break occurs if a line is broken at a soft hyphen. On the other hand, nothing is displayed if the line is not broken at a soft hyphen."

And that is exactly what is happening in Excel. It may appear in-cell but the formula-bar will only show the Soft Hyphen when the word it's used in is actually broken in the editor itself. To test this out, try to squeeze Excel and it's formula bar to a very narrow pane. You'll notice the Soft Hyphen will appear on-and-off depending if it's needed to be shown. It's rather funny that MS even mentioned the Soft Hyphen in their docs but it won't show in the matrix, probably for the same reason.

To solve your issue:

• Press Ctrl F
• Select 'Replace'
• Search for: Hold Alt and type 0173 on the numpad.
• Replace with: - A normal hyphen.

Here is a fun little demonstration from here full of "shy" hyphens. Resize the window to make them visible one by one:

Margaret­Are­You­Grieving­Over­Goldengrove­Unleaving­Leaves­Like­The­Things­Of­Man­You­With­Your­Fresh­Thoughts­Care­For­Can­You­Ah­As­The­Heart­Grows­Older­It­Will­Come­To­Such­Sights­Colder­By­And­By­Nor­Spare­A­Sigh­Though­Worlds­Of­Wanwood­Leafmeal­Lie­And­Yet­You­Will­Weep­And­Know­Why­Now­No­Matter­Child­The­Name­Sorrows­Springs­Are­The­Same­Nor­Mouth­Had­No­Nor­Mind­Expressed­What­Heart­Heard­Of­Ghost­Guessed­It­Is­The­Blight­Man­Was­Born­For­It­Is­Margaret­You­Mourn­For