Text with a dash in the cell but not in the formula bar Excel

Question

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?

Answer 1

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 Excel is actually displaying that. The Soft Hyphen will only be displayed in the formula bar when the term it is used in is really broken in the editor itself, despite the fact that it may appear in-cell. Try to cram Excel and its formula bar into a very small pane to test this out. The Soft Hyphen will intermittently emerge depending on whether it is required to be revealed. It's kind of ironic that MS even brought up the Soft Hyphen in their documentation, yet for the same reason, it won't appear in the matrix.

To solve your issue:

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