CODEWITHSUNDEEP
excelexcel-formula
Lontiac
Lontiac

Reputation: 63

MS Excel shows extra hyphen in cell

I just converted a file from PDF to excel. In each cell, it shows the correct value but when I press on the cell and look at the formula the hyphen is missing. I tried to fix this by adding the hyphen in but it just shows an extra one. How do I fix this? It is messing up all my lookups/merges.

enter image description here

This is without adding a hyphen notice how the Cell still shows 01206-NB but not the formula. enter image description here

This is when I add a hyphen to the formula the initial one stays and I end up with two.

I have tried copy and paste specials but nothing works.

Edit: When I press format cell enter image description here

Upvotes: 1

Views: 971

Answers (1)

Qualia Communications
Qualia Communications

Reputation: 715

This answer was strongly polished by Scott Crane. Thanks a lot!

enter image description here

Your data contains the Soft Hyphen (Unicode: 00AD = decimal 173).

How to get rid of it?

Method 1: Search and Replace

  • Open Search and Replace
  • Click in Find what
  • Hold down the Alt key and ender 0173 with the number pad to put the soft hyphen into the box
  • Leave "Replace" empty.

Method 2: Formula

=SUBSTITUTE(A16,UNICHAR(173),"")

enter image description here

Upvotes: 3

Related Questions