How to Get Length of String by VBA Len Function?

VBA Len function

You may use the Len function in VBA to get the length of a string, the value in the cell, etc.

Syntax of Len function

Len (String)

Len function returns a long that represents the number of characters in a string.

An example of providing a string to Len function

We just provided a text string to the Len function in this example and displayed the result in a VBA message box:

Sub inputbox_ex()

    Dim str_len As Long

    str_len = Len("VBA Tutorial")

    MsgBox "Length of String = " & str_len

End Sub

Result:

VBA Len String example output in a message box

Using a String variable example

In this example, using a String variable and then getting its length by Len function:

Sub inputbox_ex()

    Dim Str As String

    Dim str_len As Long

   

    Str = "VBA Tutorial"

    str_len = Len(Str)

    MsgBox "Length of String = " & str_len

End Sub

Result:

VBA Len String with a variable example output

Are leading and trailing spaces also count in the Len function?

In the string variable, we added four leading and four trailing spaces. See the same example output as above except for we added spaces:

Sub inputbox_ex()

    Dim Str As String

    Dim str_len As Long
  

    Str = "    VBA Tutorial    "

    str_len = Len(Str)

    MsgBox "Length of String = " & str_len

End Sub

Output:

Len lead trail spaces example

So, yes Len also counts leading and trailing spaces.

Getting the count of an Excel cell containing text

In this example, we will get the length of the cell that contains text information.

The result below shows both the sheet and message box showing cell length:

Sub inputbox_ex()

    Dim str_len As Long

    'Getting Length of Excel Sheet Cell

    str_len = Len(Range("B8").Value)

    MsgBox "Length of Cell Text = " & str_len

End Sub

Result:

String Length in Excel sheet result

What if you provide Integer, Long type variables in the Len function?

Be careful when using the Len function and you provide a variable.

In the case of Integer, Long, or other types of variables than String, it reruns the number of bytes required to store a variable.

See the example below for a clear idea:

Sub inputbox_ex()

    Dim i As Integer
    Dim j As Long

    i = 123
    j = 1223456


    MsgBox "Length of Integer type variable = " & Len(i)
    MsgBox "Length of Long type variable = " & Len(j)


End Sub

Results:

Len Integer

and

Len Long

Author - Atiq Zia

Atiq is the writer at toolsandtuts.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!