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
Saturday, 3rd February, 2007 at 7:05 am |
Hi Simon,
You are correct. The approach that I mentioned in part 1 does not work if the inherent cell format is text……something that I had not realised till now
Thanks for this post
Best Regards
Sam
Saturday, 3rd February, 2007 at 11:50 am |
Hi Simon,
To get it to work consistently I would change the formula rather than the cell format !!
=SUMPRODUCT((C6:C8)*1)
Cheating ??
Regards
Sam
Monday, 5th February, 2007 at 4:33 am |
Sam
it might be cheating, but if so I’ll let you off!
just using C6+C7+C8 works fine too.
I think this is why SUM is faster/more efficient than the other options.
cheers
Simon