String width not exceed column width

[Guest]

Given the font and size (Arial, 10), is it possible to calculate how wide a
given string will be? I have 120 columns that are 1 character wide (12
pixels). I would like to know how many columns a given string will cover.

Thank You!

 

[Guest]

Difficult for a proportional font.

 

[Mike Fogleman]

LEN(string) will give the number of characters in the string (includes
spaces). Since each column is one character wide, then this formula would be
real close to the actual number of columns used. Keep in mind not all
characters are the same width.

Mike F

 

[Guest]

See if this site helps you:

http://office.microsoft.com/en-us/excel/HA101375451033.aspx?pid=CL100570551033

 

[Mike Fogleman]

However, I just did a test and 69 characters used 33 columns. As Gary's
Student said, a really difficult answer.

Mike F

 

[Peter T]

In theory it's possible to calculate the width of each known character,
cater for mixed formats, etc. But the easiest way is to copy the text to a
suitably formatted cell in an otherwise empty column, perhaps in a hidden
sheet. Then do an Autofit and check the column width.

It's also possible to do similar in a Textbox, if anything slightly more
accurate as cells have a little padding which varies according to xl
version, but much more code.

Sub test4()
Dim rTmp As Range
Set rTmp = Worksheets("Sheet3").Range("A1")
rTmp(1).EntireColumn.ClearContents

rTmp.Font.Name = "Arial"
rTmp.Font.Size = 10
rTmp.Value = "Some text of unknown width"
rTmp(1).EntireColumn.AutoFit

MsgBox rTmp(1).EntireColumn.Width

End Sub

Regards,
Peter T

 

[Guest]

Thanks, Peter! Autofitting the column will get me close enough and any extra
padding is no problem. Nice, simple solution ... let Excel do the work!