VBA Len Function: Count String Characters in Excel

In VBA, Len returns the length of a string: Len("VBA Len") returns 7, including the space. Use it to check text size, validate input, or work out how many characters a value contains; use LenB when you need a byte count instead.

What the VBA Len function does

Len is a built-in Visual Basic for Applications function. It counts the characters in a string, including letters, digits, punctuation, spaces, and other characters stored in the text. It does not count words, and it does not ignore spaces at the beginning or end. An empty string has a length of zero.

For example, the text " VBA! " has seven characters: two leading spaces, three letters, an exclamation mark, and one trailing space. This behavior is often useful for validating entries. If a form field must contain no more than 20 characters, you can test its length before accepting or storing it.

In Excel VBA, Len is an intrinsic function, so you can call it directly in a procedure or function without creating an object or enabling a worksheet add-in. It is available in ordinary VBA projects, including code used with Excel workbooks.

Syntax and return value

The syntax is Len(string) or Len(varname). The argument can be a string expression or a variable. The function normally returns a Long integer representing the length. The alternative varname form is relevant to certain variables, including user-defined types; with a user-defined type, the meaning relates to the size used for file storage, so it is not simply a count of visible text.

Here are the usual string cases:

Len("Excel") returns 5.
Len("Excel 365") returns 9, because the space counts.
Len("") returns 0.
Len("A ") returns 3, because both trailing spaces count.

A string variable can also be passed directly. For instance, if message contains "Ready", then Len(message) returns 5. The result is a number, so it can be compared with another number, used in a loop, or assigned to a numeric variable.

Practical VBA examples

This short procedure prints several results in the Visual Basic Editor’s Immediate window. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11 and show the Immediate window with Ctrl+G. On Mac, the editor shortcut is commonly Option+F11, sometimes with Fn as well, depending on the keyboard.

Save a copy of the workbook before running VBA code that changes workbook data: macro actions generally cannot be undone with Ctrl+Z.

Sub ShowLenExamples()
    Dim sample As String
    Dim fixedText As String * 8

    ' A variable-length string counts its actual characters.
    sample = "VBA Len"
    Debug.Print Len(sample)       ' Prints 7

    ' An empty string has no characters.
    Debug.Print Len(vbNullString) ' Prints 0

    ' A fixed-length string includes its padding spaces.
    fixedText = "cat"
    Debug.Print Len(fixedText)    ' Prints 8
End Sub

To try it, open the VBA editor, choose Insert > Module, paste the procedure into the module, and run ShowLenExamples. The output appears in the Immediate window rather than in a worksheet cell. The fixed-length example is deliberately different from a normal variable-length string: assigning "cat" to an eight-character string pads the remaining positions with spaces.

Len versus LenB

Len counts characters in a string. LenB counts bytes used to represent a value. For ordinary VBA strings, which use Unicode storage, a familiar basic example is that a one-character string may have a Len result of 1 and a LenB result of 2. For example, LenB("A") returns 2 in the usual VBA string representation.

Use Len for limits expressed in characters, such as a maximum text-field length. Use LenB only when the task specifically concerns byte storage or a format that requires a byte count. The two measures are not interchangeable: a limit of 10 characters is not necessarily a limit of 10 bytes.

There is an additional distinction for user-defined types. VBA documentation describes Len as returning the size of a user-defined type as written to a file, while LenB relates to the bytes in memory, including possible padding between elements. If you are working with binary files or a declared type, check the layout and file format rather than assuming that either result is a visual text length.

Fixed-length strings, spaces, and Null

A declaration such as Dim code As String * 8 creates a fixed-length string with eight character positions. If you assign a shorter value, VBA pads it with spaces; Len(code) still returns 8. By contrast, a variable-length declaration such as Dim code As String reports the length of the text currently stored, including any spaces actually present.

This difference can explain why two variables that appear to contain the same visible letters produce different results. It can also affect comparisons, file records, and validation. If the requirement is to ignore spaces at the ends, remove them intentionally before counting—for example, with Trim$ when both leading and trailing spaces should be removed. Do not trim automatically if spaces are meaningful, such as in a password or a precisely formatted value.

An empty string and Null are also different. Len("") is zero. When the argument is a Variant containing Null, Len returns Null, rather than a numeric length. Code that may receive database Null values should test for Null first, for example with IsNull, instead of assuming every result is a number.

Using Len with Excel worksheet data

VBA’s Len and Excel’s worksheet LEN function are related but used in different places. Call Len in VBA code. Use LEN in a worksheet formula when you want the result displayed in the grid or recalculated as cell contents change. The worksheet function also counts spaces.

With the text VBA Len in cell A2, enter =LEN(A2) in another cell; it returns 7. This formula is useful for checking many rows without writing a macro. For a one-off count in VBA, the intrinsic function is simpler than calling a worksheet function through Excel’s object model.

To use the VBA function as a custom worksheet function, you can place a public function in a standard module. This example accepts text and returns its length:

Public Function TextLength(ByVal textValue As String) As Long
    ' Return the number of characters in the supplied text.
    TextLength = Len(textValue)
End Function

After saving the workbook as a macro-enabled Excel file (.xlsm), a worksheet can call the function as =TextLength(A2). This is a user-defined function, not the built-in worksheet LEN. It is intended for text; an Excel error value or a value that cannot be converted to a string may need separate handling. For simple cell-length calculations, the worksheet function usually avoids the need to enable macros.

Common mistakes and edge cases

Expecting spaces to be ignored: Len includes leading, internal, and trailing spaces. Trim first only if that matches the requirement.
Confusing character count with word count: Len counts characters, not words. Counting words requires separate logic, particularly when punctuation or repeated spaces are possible.
Using LenB for a character limit: byte counts are not the same as character counts. Choose the function that matches the rule being enforced.
Forgetting fixed-length padding: a String * n variable has a set size, so its length can remain n after you assign shorter text.
Treating Null as zero: a Null input does not produce the same result as an empty string. Check it explicitly when data may come from a database or a Variant.

For international text, a displayed symbol is not always a single stored character unit. VBA strings use Unicode, and a symbol outside the basic multilingual range can be represented by a surrogate pair; combining marks can also make one displayed glyph consist of multiple code points. As a result, a count from Len may not match what a person perceives as the number of visible symbols. If a system’s limit is defined in user-perceived characters, verify its Unicode counting rules rather than relying on a simple VBA length check.

Frequently Asked Questions

Does VBA Len count spaces?
Yes. It counts all spaces stored in the string, including leading, repeated, and trailing spaces. An empty string has length zero.

What does Len(“”) return in VBA?
It returns 0. This is different from passing a Variant containing Null, for which Len returns Null.

What is the difference between Len and LenB?
Len counts string characters as VBA represents them; LenB counts bytes. Use Len for ordinary character limits and LenB when byte size is what matters.

Why does Len return 8 when my string looks shorter?
The variable may be declared as a fixed-length string, such as String * 8. VBA pads shorter assignments with spaces, and those positions are included in the result.

Can I use Len in an Excel cell?
Use the worksheet formula =LEN(A2) to count the text in A2. VBA’s intrinsic Len is called from VBA code; a public VBA function can also be exposed to a sheet, but that requires a macro-enabled workbook and enabled macros.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.