character widths

Question

Is there a way to use =len (or a different function altogether) to return not just the count of characters in a string, but the actual character width of the string (in terms of Excel's character width units). For example, "t" and "T" both have a length of 1, but the lower case has less of a character width.

Answer 1

Hi @Richard Tillman,

According to your concern, the LEN() formula in Excel only returns the number of characters in the selected cell. It cannot return the rendered width of the text which depends on things like font, font size, cell formatting, etc, and also there is no built-in Excel formula can extract rendered text width either.

If you want to return character/string width in a cell, the best option is to use VBA script:

• In your Excel file, press Alt + F11 to open VBA editor.
• Select Insert > Module and paste this script:

Function TextWidth(ByVal rng As Range) As Double
    Dim shp As Shape
    Dim ws As Worksheet
    Application.Volatile True
    Set ws = rng.Worksheet
    Set shp = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 10, 10)
    With shp
        .Line.Visible = msoFalse
        .Fill.Visible = msoFalse
        With .TextFrame2
            .WordWrap = msoFalse
            .MarginLeft = 0
            .MarginRight = 0
            .MarginTop = 0
            .MarginBottom = 0
            .AutoSize = msoAutoSizeShapeToFitText
            .TextRange.Text = CStr(rng.Value)
            .TextRange.Font.Name = rng.Font.Name
            .TextRange.Font.Size = rng.Font.Size
            .TextRange.Font.Bold = rng.Font.Bold
            .TextRange.Font.Italic = rng.Font.Italic
        End With
        TextWidth = .Width
        .Delete
    End With
End Function

• Close VBA editor and try using the new function TextWidth to calculate the rendered text width of the cell data.

• When saving, you need to save it as Macro-enabled Workbook so Excel will keep the VBA script working.

Hope this information will help. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

Excel’s LEN (or LENB) functions only return the number of characters in a text string, not the rendered width of those characters on the worksheet. They count characters (including spaces) but have no awareness of font metrics or how wide a character appears in a cell.

There is no built‑in worksheet function that returns the visual/printed width of a string in “Excel character width units.” Any solution that needs actual pixel/character width must use external logic (for example, VBA using font measurement APIs or manual adjustment based on column width), not LEN.

---

References:

• Count characters in cells in Excel
• Count characters in cells with Excel for Mac
• LEN function