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)
- 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.
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.
- 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
- Press F5 to run the code or click on the run icon shown below.
- 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
- 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
- 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
- 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.
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
- 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.
