In VBA, use the Len function to get the length of a string: Len(text) returns a Long count of its characters. It counts spaces and other characters in the string too, so Len("Hello ") returns 6, not 5.
The Basic VBA Len Function
Len is VBA’s built-in function for checking how long a string is. Pass it a string value, variable, or expression in parentheses. The result is a number, which you can store, compare, display, or use to control other code.
For example, if a variable contains "Excel VBA", Len returns 9: five letters, one space, and three more letters. The function does not remove leading or trailing spaces, and it does not count words. It counts the characters present in the value it receives.
A compact macro that assigns text, measures it, and displays the result looks like this:
Sub ShowStringLength()
Dim textValue As String
Dim characterCount As Long
' Measure the text, including its space.
textValue = "Excel VBA"
characterCount = Len(textValue)
MsgBox characterCount
End Sub
This displays 9. Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. In the Visual Basic Editor on Windows, you can run the procedure with F5 while the cursor is inside it, or return to Excel and run it from the Macro dialog.
What Len Counts
For ordinary text, each letter, digit, punctuation mark, and space contributes one to the result. Characters such as tabs and line breaks also contribute if they are part of the string. That makes Len useful for validating input, finding unexpected whitespace, and checking whether text fits a chosen limit.
For example, a string made from "A", a tab, and "B" has a length of 3. A string containing "red", a carriage return and line feed, and "blue" has a length of 10: three letters, two line-break characters, and four letters. A cell may visually wrap text onto multiple lines without storing line-break characters, however; visual wrapping alone does not add to the string length.
Empty strings are another useful case: Len("") returns 0. A string containing one space is not empty; its length is 1. This distinction matters when a program treats blank input differently from input that contains only whitespace.
Counting Text in an Excel Cell
In Excel VBA, a cell’s value can be assigned to a string variable and measured with Len. For a one-off check, the Immediate window in the Visual Basic Editor can also evaluate a command such as ? Len(Worksheets("Sheet1").Range("A1").Value). The question mark is the Immediate window’s shorthand for printing an expression’s result; it is not part of a VBA statement you would put in a worksheet cell.
Here is a small macro that measures the displayed cell value as text and reports the count:
Sub CountCellText()
Dim textValue As String
Dim characterCount As Long
' Read A1 and convert its value to text.
textValue = CStr(Worksheets("Sheet1").Range("A1").Value)
characterCount = Len(textValue)
MsgBox "Characters: " & characterCount
End Sub
Change Sheet1 and A1 to match the workbook. CStr makes the conversion to text explicit, which is helpful when a cell might contain a number or date rather than a text value. Keep in mind that converting a value to a string can affect how it appears: a number may be converted without the cell’s number format, so the resulting text may not match what Excel displays on screen.
If you only need a worksheet result, use Excel’s worksheet function =LEN(A1) in a cell. That is a worksheet formula, not VBA syntax. In Excel for Microsoft 365 on Windows, it counts the characters in the cell value; VBA’s Len performs the corresponding task in code. The formula is also available in Excel for Mac and Excel for the web.
Leading and Trailing Spaces
Len counts spaces at the beginning and end of a string exactly as it counts spaces in the middle. For instance, the length of " cat " is 6: two leading spaces, three letters, and one trailing space. This can be useful when you need to detect accidental padding rather than silently ignore it.
If the purpose is to count after removing ordinary spaces from the ends, pass the trimmed result to Len: Len(Trim$(textValue)). The dollar-sign form, Trim$, returns a string. The related functions LTrim$ and RTrim$ remove spaces from only the left or right end. These functions are not general-purpose cleaners for every kind of whitespace; tabs and line breaks are not the ordinary space character they remove.
It is usually better to decide deliberately whether whitespace should count. Trimming too early can hide a data-entry problem, while never trimming can make a comparison or length check fail unexpectedly. If you need to detect a specific character, inspect the original string rather than relying only on its trimmed length.
Fixed-Length and Variable-Length Strings
VBA supports both variable-length strings and fixed-length strings. A normal declaration such as Dim name As String creates a variable-length string: its current content determines what Len(name) returns. Assigning "Mia" to that variable gives a length of 3.
A fixed-length string declares its storage length as part of the variable declaration, for example Dim code As String * 8. VBA pads a shorter assigned value with trailing spaces to fill the declared length. Consequently, after assigning "ABC", Len(code) returns 8, not 3. The padding is part of the fixed-length string, so it is included in the count. This is a frequent source of surprise when code compares a fixed-length value with a trimmed or variable-length one.
Use a fixed-length declaration only when a fixed-width value is intentional, such as when working with a format that expects a specific field width. For ordinary names, labels, and user-entered text, a variable-length string is generally easier to reason about. If you need the number of meaningful non-padding characters in a fixed-length string, consider whether trimming is appropriate for that data before measuring it.
Len Versus LenB
LenB is related to Len, but it returns the number of bytes used to represent a value rather than the character count. For a VBA Unicode string made up of ordinary characters, a character typically occupies two bytes, so LenB("ABC") returns 6 while Len("ABC") returns 3.
Most code that asks how many characters a person typed should use Len. LenB is for cases where byte size matters, such as certain legacy data layouts or operations involving byte-oriented structures. It is not a more precise way to count visible letters. Byte representation depends on the kind of value and the context, so do not replace Len with LenB simply because the latter sounds more exact.
Also distinguish the string’s length from the amount of memory used by an entire VBA variable. A string has storage details beyond its text, and LenB should not be treated as a general measurement of a variable’s total memory footprint.
Unicode, Emoji, and What “Character” Means
VBA strings use Unicode, which lets them hold text from many writing systems. For common letters and symbols that fit in one UTF-16 code unit, Len gives the expected one-per-character count. Some characters outside the Basic Multilingual Plane, including many emoji, are represented by a pair of UTF-16 code units. Such a symbol can therefore contribute 2 to Len, even though a person sees one symbol.
There is an additional distinction: a visually combined character may consist of multiple Unicode code points. For example, a letter and a combining accent can appear as one glyph while being stored as separate elements. Likewise, some emoji sequences combine multiple code points. In those cases, the result from Len is not necessarily the number of visible symbols a user perceives.
For typical spreadsheet checks, this technical distinction rarely affects ordinary English text. It matters when a limit is defined in user-perceived characters, especially for international text or emoji. If a system specifies a character limit, confirm whether it means UTF-16 code units, Unicode code points, bytes, or visible grapheme clusters; those are not interchangeable measures.
Common Errors and Practical Checks
A few small details can make a correct Len call produce a result that seems wrong. Check what value is actually being passed, not only what appears in the worksheet or editor.
- Whitespace is present: Leading, trailing, tab, and line-break characters can increase the count. Inspect or clean the value only when that matches the requirement.
- The variable is fixed-length: Its declared padding spaces count. Check whether the declaration uses
String * n. - The cell contains a number or date: The displayed format may differ from the string produced by conversion. Decide whether you need the underlying value or formatted text.
- The value is Null: A Variant containing
Nullis not an ordinary string. Handle that case before measuring it; converting Null directly withCStrraises an error. - The expected count is based on visible symbols: Emoji and combined Unicode characters may not correspond one-for-one with the count returned by
Len.
When a value may be empty or Null, validate it before calling other string operations. In Excel VBA, a cell’s Value can contain an error value as well, so code that reads arbitrary cells may need to check for errors before converting the value. A routine designed for known text can be simple; one that processes user-entered or imported data should define how it handles blanks, errors, and non-text values.
When to Use Len and When to Use Something Else
Use Len when your question is “How many units of text does this VBA string contain?” It is appropriate for straightforward length limits, empty-string checks, and deciding whether a string needs further processing. For example, checking whether Len(textValue) = 0 tells you whether the string is empty, but it does not tell you whether the string consists only of spaces.
For other questions, choose a function that matches the task:
- Use
Trim$,LTrim$, orRTrim$when ordinary spaces at the ends should be removed before counting. - Use
InStrto find the position of a substring, rather than treating total length as a way to search. - Use
LenBwhen byte length is specifically required, not for a normal character limit. - Use a worksheet
LENformula when the count should appear in a cell and update as worksheet values change.
Choosing the right measurement prevents a common design mistake: enforcing a limit in bytes when the requirement is characters, or enforcing code-unit length when the user-facing rule concerns visible symbols. State the unit in the requirement whenever text crosses between VBA, worksheets, files, or external systems.
Frequently Asked Questions
What does Len return in VBA?Len returns the length of its argument as a number. For a variable-length string, that is the count of the string’s UTF-16 code units; spaces and line breaks in the string are included.
Does VBA Len count spaces?
Yes. Leading, trailing, and internal spaces all count. For example, Len(" A ") returns 3.
How do I count the characters in an Excel cell with VBA?
Read the cell’s value into a string, then pass that variable to Len. For a worksheet-only result, enter =LEN(A1) in a cell instead.
What is the difference between Len and LenB?Len counts string units, while LenB reports bytes used to represent the value. For ordinary VBA Unicode text, LenB commonly returns twice the Len result.
How can I ignore spaces at the ends?
Measure the trimmed value with Len(Trim$(textValue)). This removes ordinary spaces from both ends, but does not generally remove tabs or line breaks.
Does Len count an emoji as one?
Not always. Some emoji are represented by two UTF-16 code units, and joined emoji or combined characters can contain several units. Len reports the string’s units, not necessarily the number of symbols a person sees.
Related
- Goal Seek on Multiple Cells in Excel: What Works?
- Add Slide Numbers to PowerPoint: Steps and Fixes
- Debt Payoff Spreadsheet Template: Free Excel Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macros Disabled: How to Enable Them SafelyTo enable a macro in Excel for Microsoft 365 on Windows, open a workbook you trust and select…
- VBA Macro Copy and Paste: Methods, Examples, and SafetyIn Excel VBA, copy and paste cells with the Range.Copy method: specify the source range and a destination,…
- VBA Find the Maximum Value in an Excel Column: How-ToTo find the largest numeric value in an Excel column with VBA, use the worksheet function Max on…
- Excel VBA MsgBox New Line: Add Line Breaks CorrectlyTo put a new line in an Excel VBA message box, join the text with vbCrLf: MsgBox "First…
- MS Access VBA: How to Send Email SafelyYes. Microsoft Access VBA can send email with DoCmd.SendObject for an Access report or other database object, or…
- Excel VBA MsgBox Alternatives for Better User AlertsExcel VBA alternatives to MsgBox include the status bar for brief updates, a worksheet cell for persistent messages,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.