Mystery - Excel treats the same cell contents differently

[Dale]

I saw a document somewhere in the past couples of weeks, which I wanted
to review in greater detail but now I can't find it. It stated a
problem within Excel where cell contents appear to be the same to the
eye, but not to Excel. Can anyone point me in the right direction!

 

[Ken Wright]

Probably to do with text vs numeric

Take alook at these

123
123

They are not the same - The second has a trailing space and will make it
Text as far as Excel is concerned.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Guest]

Don't know what 'document' you saw, but there are several things of that ilk
that come to mind right away.

3 examples:
'123 entered in a cell (leading apostrophe) is NOT the same as a numeric 123
"Dale " would look the same as "Dale", but Excel sees the extra space and
says they're not the same
12.499 formatted as Comma2 will LOOK like 12.50, but it's NOT

 

[Harlan Grove]

Ken Wright wrote...

Probably to do with text vs numeric

Take alook at these

123
123

They are not the same - The second has a trailing space and will make it
Text as far as Excel is concerned.

....

You don't even need different types. Unless column widths are huge or
number format is set to show 15 decimal places, both the following cell
*contents*

122.99999999
123.00000001

will display as 123.

 

[Ken Wright]

Very true, guess i was a bit quick off the mark there.

That scenario will likely be the common one, especially with numbers
generally being formatted to a rounded state.

Regards
Ken...................

 

[Dale]

I checked the format which is General in both cells, the content is
"50962" and no leading or trailing chars, but my pivot table counts
these as different things. I manually have to copy/paste to get rid of
the dup. Any thoughts...

 

[Ken Wright]

Select all your data and format as any numeric format.
Now put a 1 in any empty cell, copy that cell, select all your data and do
edit / paste special / tick values and multiply.
Now refresh the Pivot table

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------