# 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```

Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

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