VBA Len String Variable: Count Characters Correctly

VBA & macros · Updated November 18, 2026

In VBA, use Len(stringVariable) to get the number of characters in a string; the result is a Long. For example, if customerName contains Ada Lovelace, Len(customerName) returns 12, including the space.

What Len means in VBA

Len is a built-in VBA function that measures the length of a value. When its argument is a string, it counts the characters in that string. The result is useful when you need to check whether text is blank, enforce a character limit, or process text one position at a time.

The function returns a number, not a shortened or changed version of the string. Calling Len does not edit the variable, remove spaces, or change its data type. The original string remains available for later statements.

Length is a count of positions in the string, not a count of words or visible groups of text. For ordinary English text, each letter, digit, punctuation mark, and space normally contributes one position. Special Unicode text can be more complicated: a displayed symbol or accented character may be represented by multiple underlying code units, so Len is not a universal count of what a person sees as one visual symbol.

Syntax and the return value

The basic syntax is Len(string). Put the variable or expression inside the parentheses. You can assign the result to a Long, compare it in an If statement, or use it in a loop. Although VBA can often convert an expression for you, a String variable makes the intention clear when the value is text.

  • Len(text) returns the length of the string currently stored in text.
  • Len("hello") returns 5.
  • Len("") returns 0 because the string has no characters.
  • Len(" ") returns 2; those two spaces are characters.

The value is typically stored in a Long, VBA’s standard integer type for a length count. Use the result directly in comparisons rather than converting it to text. For example, If Len(text) > 50 Then checks whether the value has more than 50 characters.

A basic string-variable example

This short procedure assigns a value to a String variable, obtains its length, and prints both values to the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows.

Sub ShowStringLength()
    Dim customerName As String
    Dim characterCount As Long

    ' Count every character, including the space.
    customerName = "Ada Lovelace"
    characterCount = Len(customerName)
    Debug.Print characterCount  ' Prints 12.
End Sub

There are three characters in “Ada,” one space, and eight in “Lovelace,” for a total of 12. The assignment to characterCount stores that result; it does not alter customerName. If you change the text before calling Len, VBA measures the new value instead.

Before running a macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, even when a sample macro seems harmless.

Spaces and other characters count

Len counts spaces wherever they occur: at the beginning, between words, and at the end. It also counts punctuation, digits, and characters such as tabs or line breaks when those are present in the string. That behavior is often exactly right for a maximum-length check, because the stored text includes those characters.

It can also explain why a cell or input looks empty but produces a nonzero length. A string containing three spaces has length three, not zero. If the requirement is to treat ordinary leading and trailing spaces as insignificant, trim the text before measuring it. VBA’s Trim$ removes leading and trailing space characters; it does not remove spaces between words.

Sub CompareRawAndTrimmedLength()
    Dim enteredText As String
    Dim rawLength As Long
    Dim trimmedLength As Long

    ' The surrounding spaces count in the raw value.
    enteredText = "  VBA  "
    rawLength = Len(enteredText)              ' 7
    trimmedLength = Len(Trim$(enteredText))   ' 3
    Debug.Print rawLength, trimmedLength
End Sub

Choose deliberately between the two measurements. Use Len(enteredText) when the spaces are part of the actual input and should count. Use Len(Trim$(enteredText)) when surrounding spaces should not affect a check. Trimming before validation changes what you measure, so do not do it automatically if exact spacing matters.

Len versus LenB

Len and LenB answer different questions. Len reports string length in characters as VBA counts them. LenB reports the number of bytes used to represent the value. In typical VBA strings, which use Unicode storage, an ordinary character commonly accounts for two bytes; therefore LenB can be twice Len for simple text.

For example, the word Cat has a Len of 3 and typically a LenB of 6 in VBA. Do not use LenB to enforce a limit expressed in characters. Nor should you treat its result as the complete size of a file, a database record, or text sent over a network: those formats may encode or store text differently and may add their own overhead.

LenB is relevant when code specifically needs a byte count, such as working with certain data structures or binary representations. For everyday text checks and character limits, Len is the appropriate function. If the data will be written to a particular external format, use that format’s encoding rules to determine the actual byte size.

Using Len for validation

A common use is rejecting text that exceeds a permitted length. Compare the result with the rule you need, and make the limit easy to identify in the code. For example, a form might allow up to 30 characters in a label. “Up to 30” normally means 30 is accepted and only values greater than 30 are rejected.

Sub CheckLabelLength()
    Dim labelText As String
    Dim maximumCharacters As Long

    ' Allow values of exactly 30 characters.
    labelText = "Quarterly sales summary"
    maximumCharacters = 30

    If Len(labelText) > maximumCharacters Then
        Debug.Print "Too long"
    Else
        Debug.Print "Length accepted: " & Len(labelText)
    End If
End Sub

The example text has 24 characters, so the accepted branch runs. If you want to reject a value that is exactly at the limit, use a greater-than-or-equal comparison instead; that is a different rule. State whether the limit is inclusive when translating a form, import specification, or business requirement into VBA.

For a blank-input check, compare the length with zero. If spaces alone should count as blank, test the trimmed value instead. For a Variant that may contain Null, handle IsNull before calling or assigning the result of Len; a Null value is not the same thing as an empty string, and it cannot safely be treated as an ordinary numeric length.

Fixed-length strings and common surprises

Most VBA String variables are variable-length: they hold the text assigned to them without padding it to a declared size. VBA also supports fixed-length strings, declared with a length after an asterisk, such as Dim shortCode As String * 8. A fixed-length string is padded with spaces to its declared length when the assigned text is shorter. Consequently, Len(shortCode) reports the fixed length, including padding, rather than just the number of non-space characters originally typed.

This distinction matters when code is adapted from examples or older data-processing routines. If you see an unexpectedly high result, inspect the declaration as well as the assigned text. Also check for spaces appended by a worksheet cell, an import, or another procedure. The debugger’s Locals and Watch windows can help you inspect the variable, while printing Len before and after trimming helps distinguish padding from meaningful content.

  • Use a normal As String declaration for text whose length can vary.
  • Use a fixed-length declaration only when a specific data layout requires it.
  • Do not infer that a value is blank just because it looks blank; check whether it contains spaces.
  • When exact Unicode display length matters, test representative text rather than assuming one displayed symbol always equals one VBA position.

Related functions and choosing the right check

Use Len when the question is “How many positions are in this string?” Use Trim$, LTrim$, or RTrim$ when the rule concerns leading or trailing spaces, and call Len on the resulting text if the trimmed length is what matters. Use InStr when you need to find a substring, not count the whole string. Use Left$, Mid$, or Right$ when you need to extract part of a string.

These functions solve separate problems. For instance, a string can have a length of 20 without containing a required word, and finding a word with InStr does not tell you whether the entire input fits a character limit. A robust validation routine may use several checks, each tied to a specific requirement.

Frequently Asked Questions

How do I get the length of a string variable in VBA?
Call Len with the variable in parentheses, as in Len(myText). It returns a number representing the string’s length.

Does Len count spaces in VBA?
Yes. Leading, trailing, and internal spaces count. If surrounding spaces should be ignored, measure Len(Trim$(myText)) instead.

What does Len return for an empty string?
Len("") returns 0. A string containing one or more spaces is not empty and returns the number of spaces it contains.

Should I use Len or LenB for a character limit?
Use Len for a limit stated in characters. LenB counts bytes in the VBA representation and is intended for byte-oriented work, not ordinary character-limit validation.

Why does Len return a larger value than the visible text seems to have?
Check for leading or trailing spaces, fixed-length String padding, and characters that are not visually obvious, such as tabs or line breaks. For unusual Unicode text, the number of underlying string positions may also differ from the number of displayed symbols.

Related

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.