MS Excel column width property 1

[GSMike]

I have often wondered how that "Autofit" command works internally. I mean, I can see that it could work, if once issued, the application, runs down the column, finds the value with the greatest length, and then adjusts the column width accordingly. However, it seems to work immediately upon execution, regardless of the number of rows. Obviously, then, no such processing is taking place.
If this is true, then there must be some sort of internal record being kept about the length of values whenever a value is added.
Then, when the Autofit command is initiated, it simply refers to this internal data table, makes reference to this value and then adjusts the column width accordingly.
Assuming, again, that all of this is true, is there a way to access this piece of data. I am loading Excel data into SAS, and it would be very helpful to know the length of the longest value in a given column.
Thanking you in advance.
Mike K

 

[SkipVought]

Hi,
I am not aware of a property associated with the length of text in a column. Actually, the Columns.AutoFit method uses some complex algorithms that include the font type for determining the max column width. For Arial W is wider than ii, but for Courier, ii is wider than W.

So how are you exporting Excel data? Are you generating a .PRN file?

Let me know. Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

GSMike,

I too am not aware of such a property. However, even IF this property were capable of being captured, it would be DIFFERENT than the actual width of the widest number or text string in the column. I did some testing to confirm this.

A workaround which you might consider is the following:

1) In an adjacent column (say column "X" - off to the side of your data list), use the "LEN" function - e.g. =LEN(A10) - and do the same in column "Y" for B10, etc, for other fields you might also want to do a MAXIMUM LENGTH count on.

2) Copy these formulas down for the number of records in your data list.

3) At the top of these "LEN formula" fields, use the "MAX" function - e.g. =MAX(X10:X2000) - to give you the maximum length.

By your description, I'm not sure whether you are referring to columns with NUMBERS or TEXT, but the "LEN" function WILL work in EITHER case.

A "side note" (and a "crying" note)... Having come from a "heavy-duty" background in Lotus 123, I can tell you that Lotus 123's "database functions" are EXTREMELY powerful in comparison - wherein one can use "HUGE" COMPOUND conditions in the "criteria" of the database formula.

With Excel, I tried, and tried, but without success, to create a SIMPLE "criteria" (for a "DMAX" database formula) where the criteria formula would have been =LEN(A10). I tried SEVERAL options, but none worked.

If ANYONE out there has any advice to offer on my use of Excel's database formulas (using complex criteria), then I sure would appreciate that advice, and probably other Excel users as well.

Hope the workaround is useful. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GSMike]

SkipVought,
You are correct. There is the incorporation of fonts types, font sizes, etc. Perhaps there are values stored internally not only for the length of the cell value, but for the largest font (composite value incorporating fontstyle, fontsize, etc.) as well.
Who knows.
How I am exporting Excel is by importing it into SAS. First I save it as a CSV file, then read into SAS with their language.
Thanks for your help on this.

DaleWatson123321,
Yep, your idea worked great. That is huge. Thanks a million.
No experience with Excel database functions. Sorry. Just took a look at some help files; it does look interesting. If I figure anything out, I'll let you know.
Thanks again. Mike K