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

Advertisements

3 Responses to “The joy of text (part 2 of 2 hopefully)”

  1. sam Says:

    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

  2. sam Says:

    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

  3. Simon Says:

    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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: