UKC

I've got a few massive spreadsheets (think millions of cells not thousands). These have text in, numbers stored as text, numbers stored to 1dp, numbers stored to 2dp, dates, times and all manner of other stuff.

Is there any way of highlighting what formatting is in use in a cell, to compare sheets of similar looking data? I know you can highlight numbers stored as text using the error checking options but can I tell the difference between 0.9, 0.85 (1dp), 0.90 and 0.9 (stored as text)?

using =Sheet1 A1 = Sheet2 A1 etc isn't descriminating enough

Ideally I either need to output these differences in conditional formatting (ie. Numbers stored to 1dp in blue) or spitting out a numeric value for the formatting that I can play with in a function. (I know you can read cell colours with some VBA code).

Cheers!

In reply to Reach>Talent:
=cell("format",ref)

In reply to dissonance:

this gives you the list of potential outputs and also other choices.

http://www.techonthenet.com/excel/formulas/cell.php

Only question would be how it deals with custom categories.
If i was dealing with millions of cells though i would be tempted to go the vba route from the outset. Otherwise you would need to set auto calc on and off as needed which is a pain.

In reply to dissonance:
Cheers my Googling skills are obviously weak this morning, I should have spotted that!

(Un)fortunately it is not my data so there are limits on what I can do with it, also I have to explain any changes I make and I don't want to explain VBA unless I absolutely need to

In reply to dissonance:
Just spotted the lack of irritating lack of updating. I can always work around that though!