The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Find data width in Excel

In Excel, is there a way to find out the character width of the data in a column?
I have a column with 60,000 records and each record contains Street names. I want to know what is the longest possible street name. I also want to look at the distribution of the character or pixel width of all the street names. Similarly, I have about 100 other columns with 60,000 records which contain varying length of text, numeric or memo field data and again I want to find the character width edge cases and distribution. The end goal is to give guidance to UI on mean and edge case character width so they can decide on column width and word wrap.
Excel functions work with the data itself while I am interested in the width of the data.
Any idea how one could possibly do this? If its possible in Visual Basic, can you provide step by step directions since I am a novice in programming.
Tuesday, September 26, 2006
There is the LEN() function that will tell you the number of characters in the cell. But I don't think this is exactly what you are looking for...
Tuesday, September 26, 2006
Found it! Its
Tuesday, September 26, 2006
And if you really want to be sneaky...

Len will tell you the length of the string in an individual cell but not how long the longest length is. You can create a formula and copy it down for 60,000 cells or loop through each cell in code or use an array formula like this:


where A2:A60000 is the range of cells you want to find the longest string length in.

Type the formula as: =MAX(LEN(A2:A60000))
and then press CTRL+SHIFT+ENTER to submit the formula.
Excel will surround the formula with { } for you.
Marcus from Melbourne
Tuesday, September 26, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz