Repost: How to calc string's printable width?

[count]

Hi,
Task at hand is to achieve this effect to print a directory:
Short Company Name....................Meredith Rockefeller
Long Company Name Truncated If Nece.......Judy Short

What we have here is left/right justified concatenation of:
LEFT(CompanyName,x) & "...y.dots..." & Contact
So we seek a solution to figure out x and y. Maybe some library offers such
facility?
AutoFill certainly manages to work out the widths 1 by 1 but can this be
bettered?
TIA,
Paul
PS. Task gets urgent so forgive the crosspost

 

[Stephen Bullen]

Hi Paul,

Task at hand is to achieve this effect to print a directory:
Short Company Name....................Meredith Rockefeller
Long Company Name Truncated If Nece.......Judy Short

What we have here is left/right justified concatenation of:
LEFT(CompanyName,x) & "...y.dots..." & Contact
So we seek a solution to figure out x and y. Maybe some library offers such
facility?
AutoFill certainly manages to work out the widths 1 by 1 but can this be
bettered?

One possibility is to use a text box on the sheet set to AutoSize and not
word wrap, put each half of your text in it, then read the width. However,
when you come to print the sheet, the printer font substitution will probably
misalign the right-hand sides anyway. It might be better to use Word instead,
where you can set a flush-right tab stop.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk