VBA Get Length of String: How the Len Function Works

VBA & macros · Updated November 18, 2026

To get the length of a string in VBA, use the Len function: Len(myText) returns the number of characters in the string. For example, Len("Hello") returns 5; spaces and punctuation count too.

The basic VBA string-length function

Len is VBA’s built-in function for measuring the length of a string. Supply a string expression, variable, or value, and it returns a Long integer. You do not need to import a library or create a custom function.

The simplest use is to assign text to a variable and pass that variable to Len. This small macro displays the length of “Hello, VBA!”—11 characters, including the comma, the space, and the exclamation mark.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.

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

    ' Store the text and count every character, including spaces and punctuation.
    textValue = "Hello, VBA!"
    characterCount = Len(textValue)

    MsgBox characterCount
End Sub

In the Visual Basic Editor, you can put this procedure in a standard module and run ShowStringLength. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11; on a Mac, the shortcut is Option+F11 (on some keyboards you may also need Fn). You can also open the editor from the Developer tab. The result is a message box showing 11.

What Len counts

Len counts characters in the string value, not words, visible symbols, or the width of the text on screen. A regular space is one character. So are a comma, a line break stored in the string, and each letter in a word. For instance, “red blue” has a length of 8: three letters, one space, and four letters.

The function examines the value itself. It does not look at how a cell is formatted or how much room the text takes up in a column. A long sentence displayed in a wide cell and the same sentence displayed in a narrow cell have the same length. Font, font size, and column width do not affect the result.

For ordinary English text, the count usually matches what people mean by “number of characters.” There is a Unicode nuance for less common text: VBA strings use UTF-16, so some characters outside the basic multilingual plane may occupy two code units and be counted as two. A displayed character made from a base letter plus a combining mark can also contain more than one underlying character. If your definition of a character means one user-perceived symbol, test the specific text rather than assuming Len counts visual symbols.

Using Len with string variables and expressions

You can pass a literal, a variable, or a string-producing expression to Len. That makes it useful in validation, loops, and conditional logic. For example, an input form can check whether a user entered anything before attempting to process the value. A filename routine can reject names over a chosen limit, or a report macro can identify unusually short descriptions.

The function returns a number; it does not change or shorten the original string. Store that number in a numeric variable such as Long if you need to compare it or use it later. A typical condition is conceptually “if the length is zero, treat the string as empty.” For fixed-length text fields or required input, a separate condition may be needed to distinguish blank text from text containing only spaces.

When text comes from a variable declared as String, pass it directly. Avoid converting it to another type without a reason. If the expression is a Variant containing a number or date rather than text, VBA may coerce it to a string in some contexts, but an explicit conversion makes your intention clearer. Be aware that the length then applies to the converted representation, whose formatting can depend on the value and regional settings.

Getting the length of text in an Excel cell

For a cell containing text, pass its value to Len. A common pattern is to use a worksheet range’s Value2 property, which reads the cell value without Excel’s Currency or Date subtype handling. The count is for the stored value, not necessarily the characters shown by the cell’s number format.

That distinction matters when a cell contains a number formatted to show leading zeros, a date displayed in a longer date style, or a value displayed with a custom format. The underlying value and its visible text can differ. If you specifically need the displayed text, the range’s Text property returns what Excel shows, but it can return a string of hash marks when the column is too narrow. Widen the column or use the underlying value if that better matches the task.

Also consider cells containing Excel errors. Passing an error value directly to a string conversion can cause a type mismatch. If the source range might contain errors, check for them before converting or counting. For a range of ordinary text values, the key choice is simple: count the cell’s value when you care about the stored content, or inspect its displayed text only when the presentation is what matters.

Len versus LenB

Len and LenB answer different questions. Use Len for the character count of a string. LenB reports the number of bytes used to represent the string in VBA’s internal representation. For example, a simple three-letter VBA string commonly has Len equal to 3 and LenB equal to 6 because VBA stores string characters as two-byte Unicode code units.

That byte count is not necessarily the same as a file’s size after saving text. File encoding, such as UTF-8, can use a different number of bytes per character, and the file may include encoding markers or other data. Do not use LenB as a general way to predict the size of exported text. Choose an encoding-aware method when you need the actual byte count for a file or network transfer.

LenB also has a specific use with user-defined types: it can report the memory size of a variable of that type. That is a different task from counting characters and may involve padding or layout details. For most VBA code that checks how many characters a user typed, Len is the appropriate function.

Spaces, empty strings, and other edge cases

An empty string has length zero, but a string containing spaces does not. For example, three spaces have a length of 3. This is a frequent source of unexpected validation results: checking whether Len(value) = 0 only catches a truly empty string, not an entry that contains whitespace.

If leading and trailing ordinary spaces should be ignored, use VBA’s Trim$ function before measuring. It removes spaces from the beginning and end, so a value containing only ordinary spaces becomes empty for this check. It does not remove every possible whitespace character: tabs and nonbreaking spaces, for example, may need separate handling. Also, VBA’s Trim$ does not collapse repeated spaces between words. That behavior is different from Excel’s worksheet TRIM function, which also reduces repeated internal spaces.

Other values deserve care. A Variant containing Null is not the same as an empty string; attempting to use a Null result where a number is required can raise an error. A fixed-length string declared with a size, such as String * 10, is padded with spaces to its declared length, and those spaces count. If you use fixed-length strings, account for the padding rather than assuming the variable contains only the text assigned to it.

Common mistakes and how to choose the right method

  • Using the wrong name: VBA calls the function Len, not Length. The Excel worksheet function is named LEN; VBA function names are not case-sensitive.
  • Forgetting spaces count: If your business rule means nonblank after trimming, measure a trimmed value rather than the original string.
  • Counting formatted cell output by accident: Decide whether you need the underlying value or the text Excel displays. A number formatted with leading zeros illustrates why those can differ.
  • Confusing characters with bytes: Use Len for a character count and investigate encoding separately if you need a file’s byte size.
  • Ignoring unusual input: Handle Nulls and cell errors before converting values to strings, and test non-ASCII text if exact Unicode counting matters.

If you only need a count in a worksheet cell, Excel’s worksheet function is usually simpler: enter =LEN(A1) in another cell to count the text in A1. If you need to make a decision, clean text, or process many cells as part of a macro, use VBA’s Len in your code. In Excel for the web, you can use worksheet formulas, but you cannot create or run VBA macros there; use the desktop application for VBA.

Frequently Asked Questions

How do I get the length of a string in VBA?
Pass the string or string variable to Len, as in Len(myText). The function returns a Long count.

Does Len count spaces and punctuation?
Yes. Every ordinary space and punctuation mark in the string contributes to the count, as do line breaks stored in the string.

How can I ignore spaces at the beginning and end?
Use Len(Trim$(myText)) to count after removing leading and trailing ordinary spaces. Tabs and other whitespace characters may require additional cleanup.

What is the difference between Len and LenB?
Len counts characters in a VBA string. LenB counts bytes in the string’s internal representation, so it is not a substitute for a character count or necessarily the byte size of an exported file.

Can I count a cell’s text with VBA?
Yes. Pass the cell’s value to Len. Use the underlying value when that is what you want to measure; use the cell’s displayed text only when the formatted appearance is relevant, and account for the possibility of hash marks in a narrow column.

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.