Workbook columns formatted as Text - Clusters too, please

May 26, 2009 at 6:30 PM

First, thanks for version 85!  It always feels like Christmas when a new build arrives, with all sorts of wonderful goodies :-)

I'm grateful that you've now default-formatted some input columns as Text, since some of my data is exactly of the kind that gets misread as dates.

However, the Vertex column in the Cluster Vertices sheet still seems to be formatted General, so that when I auto-create clusters a bunch of them get renamed as dates, and thus don't get colored in the chart.  I tried formatting the stub cell in the Vertex column as Text before running the clustering tool, but it seemed to be reset to General by the code.

In the interim, could you recommend a formula to flip them back?  I can (and do) go through the list by hand, but with 700+ vertices it gets rather tedious.  Excel also re-formats "both ways", that is, generating 27-Jul for the vertex "07-27" and Jul-54 for "03-54"; it's too clever by half ;-)

 

Great work, and thanks

Pierre

May 26, 2009 at 9:11 PM

Oh, no...  I looked all over for unwanted conversions but somehow missed that one.  I will fix it in the next release.

I've been trying to find a manageable formula or technique to reverse the damage after the fact, but I'm stumped.  Excel is just too clever for me.

If you're able to start again and recreate your clusters, this might be a short-term workaround until the real fix is released:

* Before creating clusters, do a global search-and-replace on the entire workbook, replacing every hyphen with a tilde.

* Create clusters.

* Replace all the tildes with a hyphen.

Thanks for letting me know about this.

-- Tony

 

Jun 3, 2009 at 1:02 AM

Something that somebody else might also not have known:

It turns out (see http://support.microsoft.com/kb/214138) that Excel uses the tilde as a marker to indicate that the next character is a literal.  If you want to find or replace a tilde in a worksheet, you must type a double tilde (~~) in the Find what box.

Jun 3, 2009 at 4:28 AM

That's good to know.  By the way, I've fixed the original problem you reported.  It will be in a new release later this week.

Thanks,
    Tony