How to Use VBA Len Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will describe the details of the VBA Len function along with some examples. Likewise, excel LEN function, VBA Len returns the length of a string. It can be used independently or along with other string functions such as the VBA Mid and VBA Right functions.

Len Function in Excel VBA: Syntax

  • Description

VBA Len is a String function in excel. This function returns the length of a supplied string. In addition, the VBA Len function returns the number of bytes needed to store a supplied variable.

  • Syntax

Len(Expression)

Introduction to VBA Len Function

  • Arguments
Argument Requirement Explanation
Expression Required Expression is the value or string of which we need to know the length. For example,  the expression “Good Morning” will return 12 as length.
  • Return Value

VBA Len function returns the number of characters in a supplied string.


1. Using VBA Len Function in Excel to Count Characters in a String 

As we have mentioned earlier in this article, VBA Len returns the count of the characters in a string. Depending on the code, we can show the result of the code in two forms, in the message box or directly in the corresponding excel sheet.

1.1. Show String Length in Worksheet

First, we will see how to show results in excel worksheets. For example, we have a text string ‘New York’ in Cell B5. Now I will show the length of the string on Cell C5.

Show String Length in Worksheet

Steps:

  • Firstly, go to the worksheet that contains the above data. Then right-click on the sheet name and click on the View Code option.

Show String Length in Worksheet

  • As a result, Microsoft Visual Basic for Applications window will show up. Write the below code on the Module.
Sub LenText()

Range("C5").Value = Len(Range("B5"))

End Sub

Show String Length in Worksheet

  • Press F5 to run the code or click on the run icon shown below.

Show String Length in Worksheet

  • Upon running the code, we will get ‘8’ in Cell C5, which is the character length of ‘New York’.


1.2. Show String Length in Message Box

Now, we will see how to display string length in the message box. For instance, we will find out the character length of the text: Hello New York.

Steps:

  • Right-click on the specific worksheet name and click on the View Code option to bring the VBA window.
  • Then type the below code in the Module.
Sub LenText1()

MsgBox Len("Hello New York")                    'Result is: 14

End Sub

Show String Length in Message Box

  • Press F5 to run the code. Consequently, we will receive the text length in a message box which is ‘14’.

Read More: How to Use MsgBox Function in Excel VBA


2. Applying VBA Len Function to Count Occurrence of a Character

We have mentioned earlier that VBA Len can be used with other functions. So, now we will use it with the VBA Replace function. Using VBA Len, we can find out how many times a particular character is found in a string. For example, we will find how many times the character “,” is present in the string: “Apple,Orange,Banana,Grapes,Plum“. Here, we will use VBA Replace to remove commas (“,”) with “”.

Steps:

  • Go to a blank worksheet and right-click on the sheet name. Click the View Code option to get the VBA.
  • Write the following code in the Module.
Sub LenReplace()

Dim Value As String 'Define a string variable
Value = "Apple,Orange,Banana,Grapes,Plum"
MsgBox Len(Value) - Len(Replace(Value, ",", "")) 'Result is: 4
'Break down of the code above
MsgBox Len(Value)                       'Result is: 31
MsgBox Replace(Value, ",", "")          'Result is: "AppleOrangeBananaGrapesPlum"
MsgBox Len(Replace(Value, ",", ""))     'Result is: 27
MsgBox Len(Value) - Len(Replace(Value, ",", "")) 'Result is: 31-27=4

End Sub

Apply VBA Len to Count Occurrence of a Character

  • Press F5 to get the result in the message box. The ultimate result is ‘4‘ which means the character comma is present 4 times in the supplied string.

Read More: VBA Format Function in Excel


3. Counting Occurrence of Substring Using VBA Len Function in Excel

In Method 2, we found out the number of occurrences of a single character in a given string. However, this time we will find out the occurrence of a substring (“, ”) i. e. comma and space, in a supplied string: “Apple, Orange, Banana, Grapes, Plum“.

Steps:

  • Go to a blank worksheet and right-click on the sheet name. Click on the View Code option, the VBA window will appear.
  • Then, type the below code in the Module.
Sub LenSub()

Dim Value As String 'Define a string variable
Value = "Apple, Orange, Banana, Grapes, Plum"
Dim SubStr As String 'Define a substring variable
SubStr = ", "
'We will find out how many times SubStr is occurring inside Value
MsgBox (Len(Value) - Len(Replace(Value, SubStr, ""))) / Len(SubStr) 'Result is: 4
'Break down of the code above
MsgBox Len(Value)                       'Result is: 35
MsgBox Replace(Value, SubStr, "")          'Result is: "AppleOrangeBananaGrapesPlum"
MsgBox Len(Replace(Value, SubStr, ""))     'Result is: 27
MsgBox Len(Value) - Len(Replace(Value, SubStr, "")) 'Result is: 35-27=8
MsgBox (Len(Value) - Len(Replace(Value, SubStr, ""))) / Len(SubStr)
'Result is: (35-27)/2=4

End Sub

Count Occurance of Substring Using VBA Len Function

  • Press F5 to run the code. We got the result as ‘4’ in the message box which indicates the substring (“, “) is found four times in the given string.

Read More: How to Use VBA StrComp in Exce


4. Using VBA Len Function as Support Function to Extract Text

Now I will use VBA Len as a support function along with other VBA functions. For example, I have a dataset containing some color code and color written in single cells. So, now I will extract color codes from the joined data in one cell and colors in another cell.

Use VBA Len as Support Function

Steps:

  • Go to the sheet that contains the dataset and right-click on the sheet name at first. Click on the View Code option. That will bring you to the VBA window.
  • Write the below code in the Module.
Sub LEN_Support()

Dim JoinedValue As String
Dim r As Long
For r = 5 To 9
'My dataset started from 5th row and ends at 9th. 2, 3, 4 are the column index of my data
'Based on your dataset change the numbers
JoinedValue = ActiveSheet.Cells(r, 2).Value
'This will extract first 4 characters i.e Code Portion
ActiveSheet.Cells(r, 3).Value = Left(Trim(JoinedValue), 4)
'This will extract the color portion
ActiveSheet.Cells(r, 4).Value = Mid(Trim(JoinedValue), 5, Len(Trim(JoinedValue)) - 4)
Next

End Sub

Use VBA Len as Support Function

  • Press F5 to run the code and consequently, the joined text string is split into two text strings (Code, Color).

Read More: How to Execute VBA Function Procedure in Excel


Things to Remember

➤ In case the supplied string is NULL, VBA Len will return NULL as output.

➤ We should not use the Object variable in this function.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several examples to use the VBA Len function in excel elaborately. Hopefully, these examples and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo