Excel VBA LEN Function to measure length of text

This Excel VBA tutorial explains how to use LEN Function to measure length of text.

Excel VBA LEN Function

Excel VBA LEN Function is to measure the length of text, to be more precise, the number of characters in a text.

LEN Function is most useful as a tool for data validation where length of text always have limit in a system.

LEN Function is also commonly used in conjunction with other functions:

LEFT Function – extract a substring on the left

RIGHT Function – extract a substring on the right

MID Function – extract a substring in the middle of the string

INSTR Function – return starting position of a substring (first occurance) in a string

Syntax of VBA Excel LEN Function

The Syntax of VBA LEN Function is same as that in spreadsheet.

LEN(text)
text The text string from which you want to return length

Example of Excel VBA LEN Function

Example 1

Suppose Cell A1 contains address Flat A, ABC Street,

Cell B1 contains a full name Peter, Gilbert

Formula Result Explanation
LEN(Range(“A1”).Value) 19 Total 19 characters in the string (including space)
LEFT(Range(“A1”).Value,LEN(Range(“A1”).Value)-1) Flat A, ABC Street Extract substring from left excluding the ending comma
MID(Range(“B1”).Value,INSTR(Range(“B1″).Value,”,”)+1,LEN(Range(“B1”).Value)) Gilbert Extract substring from (comma position+1) to (length of whole string)

Example 2

The below example demonstrates how to count number of “e” in the string “Peter, Gilbert” by looping through each character in a string.

Public Sub countE()
    Dim Val As String
    Val = "Peter, Gilbert"
    For i = 1 To Len(Val)
        If Mid(Val, i, 1) = "e" Then
            counter = counter + 1
        End If
    Next i
    MsgBox (counter)
End Sub

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *