VBA LEN

VBA LEN Function

VBA LEN function returns the “length of the string” i.e. it returns how many characters are there in the supplied value. Of all the string functions in VBA, “LEN” is the most under-utilized function. I have seen “VBA LEN” function used as the support function for other string function like VBA MID Functions and RIGHT functions.

How do you find the length of the string or value?

For example, if you the sentence “Hello Guys, Good Morning!!!” and you if you want to find the number of characters in it how do you find it? In this article, we will show you about the “VBA LEN” function.

Formula of VBA LEN Function

LEN function has only one syntax i.e. Expression.

An expression is nothing but the value we are trying to test.

For example, Len (“Good”) will return 4.

Examples

Below are the examples of VBA Length of String Function.

Example #1

Length of a VBA String function is very simple to use. For example, look at the below VBA code.

Code:

Sub LEN_Example()

    Dim Total_Length As String

    Total_Length = Len("Excel VBA")

    MsgBox Total_Length

End Sub

In the above code variable is “Total_Length”.

Dim Total_Length As String

For this variable, we have assigned the value through the VBA LEN function.

Total_Length = Len("Excel VBA")

For LEN function we have assigned the value as “Excel VBA”.

Total_Length = Len("Excel VBA")

Next, we are showing the result in the VBA message box.

MsgBox Total_Length

When I run this code using F5 key or manually, we will get 9 as a result because space is also a character.

VBA LEN as Support Function

Example #1

The purpose of the LEN function mostly utilized with other functions. I have used this function with RIGHT & Instr functions.

For example, look at the below sample data.

From the above data, we need to extract Date separately and Remarks Separately. Copy the above data to your excel sheet and paste in Cell A1.

In order to extract these elements, we need to use LEN function with other string function. Below code will do the job for us.

Code:

Sub LEN_Example1()

    Dim OurValue As String

    Dim k As Long

    For k = 2 To 6
'In this case my data started from second cell and ends at 6th.
'Based on your data change the numbers

        OurValue = ActiveSheet.Cells(k, 1).Value

       'This will extract first 10 characters i.e. Date portion
        ActiveSheet.Cells(k, 2).Value = Left(Trim(OurValue), 10)

       'This will extract teh remarks portion
        ActiveSheet.Cells(k, 3).Value = Mid(Trim(OurValue), 11, Len(Trim(OurValue)) - 10)

     Next

End Sub

When we run this code manually or through F5 key, we will get the result like the below.

Example #2

Now I will show you how to use VBA Length of string as a support function to extract the last name of the full name with RIGHT & Instr function.

For demonstration look at the below data.

From the above list, we need to extract the Last Name from the full name. Below code will extract the last name. LEN used as a support function here.

Code:

Sub LEN_Example2()

    Dim FullName As String

    Dim k As Long

    For k = 2 To 8

        FullName = ActiveSheet.Cells(k, 1).Value

        'This will extract last name
       ActiveSheet.Cells(k, 2).Value = Right(FullName, Len(FullName) - InStr(1, FullName, " "))
        'LEN finds full number of characters
        'Instr finds space character
        'LEN - Inst will gives total characters from the right
     Next

End Sub

Run the code using F5 key or you can run manually and see the result.

You can download this Excel VBA Length of String here – VBA LEN Function Template

Recommended Articles

This has been a guide to VBA LEN Function. Here we learn how to use LEN Function in VBA to find the length of the string along with practical examples and downloadable codes. Below are some useful excel articles related to VBA –

• VBA Class Modules
• Left Function in VBA
• VBA Sub
• VBA For Next Loop