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!

Difficult for a proportional font.
--
Gary''s Student - gsnu200750


"Norm Lundquist" wrote:

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!

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
"Norm Lundquist" wrote in message
...
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!

See if this site helps you:

http://office.microsoft.com/en-us/ex...CL100570551033

"Norm Lundquist" wrote:

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!

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

Mike F
"Mike Fogleman" wrote in message
m...
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
"Norm Lundquist" wrote in
message ...
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!

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


"Norm Lundquist" wrote in message
...
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!

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!



"Peter T" wrote:

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


"Norm Lundquist" wrote in message
...
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!