I must have clicked the wrong thing

[Lake Oswego]

Formulas which used to calculate, then disappear leaving only the answer, now
remain without showing the answer. If I replace = with =, it works but only
in that case. How can I get back to normal?

 

[T. Valko]

It sounds like the cells are formatted as TEXT.

Change the format to GENERAL.

Then, select the cell and either double click or hit function key F2 then
hit Enter.

If you have a bunch of cells to convert...

Select all the cells in question
Change the cell format to GENERAL
With the cells still selected
Goto the menu Edit>Replace
Find what: = (equal sign)
Replace with: = (equal sign)
Replace All

 

[Lake Oswego]

Excellent work! I did convert that column to text because I was getting
desperate about my Match not working and was trying everything. I have found
several people who have experienced two cells that look and smell the same
but Match won't treat them as the same. We have to be talking about some
hidden formatting that causes Exact to see a difference when we don't.

 

[Arvi Laanemets]

Hi

Excellent work! I did convert that column to text because I was getting
desperate about my Match not working and was trying everything. I have
found
several people who have experienced two cells that look and smell the same
but Match won't treat them as the same. We have to be talking about some
hidden formatting that causes Exact to see a difference when we don't.

No formatting can affect the real content of cell.

Probably you have some trailing or leading spaces in some cells. Or some
other characters which look like spaces. Or some 'hard returns'/'line
feeds'. And there is a minor possibility that some character in cell is
typed using different code (p.e. unicode character is used instead ordinar
one).


Arvi Laanemets

 

[Arvi Laanemets]

An Example about cahracters looking same but being different:
A <> Α
The first one is ordinary A (ASCII code 65)
The second one is different (unicode character with code 913)


Arvi Laanemets

 

[JLatham]

The problem could also because by internal rounding error. There may be a
tiny fraction of a decimal not being seen, and so 0<>0, or so it may appear.

Here's an example:
In A1 type the formula
=64.4 - 64
in B1 simply type in .4
in C1 put the formula =IF(A1=B1,"Equal","Not Equal")
The result will be "Not Equal" because the calculation 64.4-64 actually
returned
0.400000000000006 !

If this seems to be the problem, you can try ROUND() or even change the
Excel Options to "Precision as Displayed", just be cautious with that last
one.