How to Get Length of String by VBA Len function?

VBA Len function

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

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:

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:

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:

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:

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: