The joy of text (part 2 of 2 hopefully)

This follows on from joy of text (https://smurfonspreadsheets.wordpress.com/2007/01/22/the-joy-of-text-long/#comments)
I didn’t explain well enough what I was on about, so here is an example that might clear things up.

www.codematic.net/files/joyoftext2.zip
Download this zip from codematic, open the .xls and the .xll, try and change C7 to be treated consistently as a number, whilst maintaining its text numberformat. Column E contains the UDF CellDataType, that tells you how Excel is treating the cells currently. This is defined in the xll.

Note this is a specific issue around the text numberformat, and quite a different issue from imported data that gets incorrectly treated as text temporarily. The solutions proposed last time around fix the latter case, but not as far as I can see the former. Unless you know different??

The purpose of this couple of posts is to highlight the issue, I don’t see it as a bug in Excel at all. If you tell Excel to treat a cell as text and it does, how can that be wrong? But it might be worth using the tools that Excel provides just to make sure you didn’t do it by mistake.

I wrote the xll a while ago, as I couldn’t find the ‘.datatype’ property of a range, that I was looking for. However a quick google the other day and I found John Walkenbach had a VBA UDF to do the same thing. Oh well, I’m sure the xll version will be faster.

http://j-walk.com/ss/excel/tips/tip62.htm

cheers
Simon