Fitting characters of a font size in a cell width

[Guest]

In my application, printing a route delivery 'book', I form character
strings made up of numerals, special characters such as parentheses and
brackets, and uppercase letter text in Arial font, size 20. (I would rather
NOT use a non proportional font such as Courier). The font size IS needed
for readability. The strings can range from 4 to 20 or 30 characters.

It's important that the entire string shows and is not 'view truncated' by
the contents of the cell to the right of the one holding the string. Is there
some table to look up character widths, or some other method by which I can
calculate the 'width' of the character string to compare it to the column
width which is 27. I can break up the character string if it's too long and
put some of it in the next row.
(the default font for the worksheet is Arial, size 10.)

Visual inspection of the affected cells , after the fact, is not really
feasible. I am looking for a way to calculate the width of the string, and
then use from 1 to ??
rows to hold what has to be read by the delivery staff.

Thanks,
Neal Z

 

[Alan]

In my application, printing a route delivery 'book', I form character
strings made up of numerals, special characters such as parentheses
and brackets, and uppercase letter text in Arial font, size 20. (I
would rather NOT use a non proportional font such as Courier). The
font size IS needed for readability. The strings can range from 4 to
20 or 30 characters.

It's important that the entire string shows and is not 'view
truncated' by the contents of the cell to the right of the one
holding the string. Is there some table to look up character widths,
or some other method by which I can calculate the 'width' of the
character string to compare it to the column width which is 27. I
can break up the character string if it's too long and put some of it
in the next row. (the default font for the worksheet is Arial, size
10.)

Visual inspection of the affected cells , after the fact, is not
really feasible. I am looking for a way to calculate the width of the
string, and then use from 1 to ??
rows to hold what has to be read by the delivery staff.

Thanks,
Neal Z

Hi Neal,

I may be missing something here, but why not just use the:

Edit - Fill - Justify

functionality?

If you need to do it in VBA, then something like:

Range("A1:A10").justify


HTH,

Alan.

 

[Guest]

I'll try it, but I was not complete in my explanation. A row in the book has
only 3 columns, each with a width of 27. Each column has a specific meaning
and usually 1 row is enough to hold the needed info. The oddball, tho
important, situation is where more than one row is needed to hold the data,
which must remain at font size 20 for easy readability. I'll try your
solution. Thanks.

 

[NickHK]

Neal,
Can you not WrapText and make the row taller ?

NickHK

 

[Guest]

Not really. The route books are used in the middle of the night in a
semi-dark car.
The 'look' has to be consistant. I know I'm getting kind of nit-picky here,
but I may have to settle for a standard mix of characters, and if the count
is more than 12 or 13, split the text into different rows.