VBA Len Function – Get String Length
In this Article
This tutorial will demonstrate how to use the Len VBA function to get the length of a string.
Len Function
The VBA Len function returns the length of a specified string.
VBA Len Count Characters
The VBA Len function counts the characters in a string.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub LenExample_1() MsgBox Len("12345") 'Result is: 5 MsgBox Len("12") 'Result is: 2 MsgBox Len("1") 'Result is: 1 MsgBox Len(" ") 'Result is: 1 'There is a space character in there. MsgBox Len("") 'Result is: 0 MsgBox Len("AB Cd") 'Result is: 5 End Sub |
VBA Len Strings or Variants
VBA Len Function can count the number of characters in variables declared as strings or variants. Actually, VBA Len will treat a variant as a string. If VBA Len is used with an integer, long, single or double then VBA Len is going to count the number of bytes needed to store the variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Sub LenExample_2() Dim VarEx1 As String VarEx1 = 12345 MsgBox Len(VarEx1) 'Result is: 5 'Len is counting the number of characters in variable Dim VarEx2 As Variant VarEx2 = 12345 MsgBox Len(VarEx2) 'Result is: 5 'Len is counting the number of characters in variable Dim VarEx3 As Integer VarEx3 = 12345 MsgBox Len(VarEx3) 'Result is: 2 'Len is counting the number of bytes used to store the variable Dim VarEx4 As Long VarEx4 = 12345 MsgBox Len(VarEx4) 'Result is: 2 'Len is counting the number of bytes used to store the variable Dim VarEx5 As Single VarEx5 = 12345 MsgBox Len(VarEx5) 'Result is: 2 'Len is counting the number of bytes used to store the variable Dim VarEx6 As Double VarEx6 = 12345 MsgBox Len(VarEx6) 'Result is: 2 'Len is counting the number of bytes used to store the variable End Sub |
VBA Len Count Occurrences of a Character
VBA Len function can be used with VBA Replace function to count how many times a character is found in a string.
VBA Replace Function can replace a substring with another substring in a text:
|
1 |
MsgBox Replace("XBCX", "X", "7") 'Result is: "7BC7" |
We can use Replace to remove the characters we want to count with “” and then find the difference in length before and after the replacement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub LenExample_3() Dim StrEx As String 'Define a string variable StrEx = "Jack,John,Jim,Jordan" MsgBox Len(StrEx) - Len(Replace(StrEx, ",", "")) 'Result is: 3 'Breaking down the code above MsgBox Len(StrEx) 'Result is: 20 MsgBox Replace(StrEx, ",", "") 'Result is: "JackJohnJimJordan" MsgBox Len(Replace(StrEx, ",", "")) 'Result is: 17 MsgBox Len(StrEx) - Len(Replace(StrEx, ",", "")) 'Result is: 20-17=3 End Sub |
VBA Len Count Occurrences of a Substring
VBA Len function can be used with VBA Replace function to count how many times a substring is found in a string.
VBA Replace Function can replace a substring with another substring in a text:
|
1 |
MsgBox Replace("XB cX", "X", "7") 'Result is: "7B c7" |
We can use Replace to remove the substrings we want to count with “” and then find the difference in length before and after the replacement. Finally, we need to divide the difference with the length of the substring we replaced.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub LenExample_4() Dim StrEx As String 'Define a string variable StrEx = "Jack, John, Jim, Jordan" Dim SubStr As String 'Define a substring variable SubStr = ", " 'We will find how many times SubStr is found inside StrEx MsgBox (Len(StrEx) - Len(Replace(StrEx, SubStr, ""))) / Len(SubStr) 'Result is: 3 'Breaking down the code above MsgBox Len(StrEx) 'Result is: 23 MsgBox Replace(StrEx, SubStr, "") 'Result is: "JackJohnJimJordan" MsgBox Len(Replace(StrEx, SubStr, "")) 'Result is: 17 MsgBox Len(StrEx) - Len(Replace(StrEx, SubStr, "")) 'Result is: 23-17=6 MsgBox (Len(StrEx) - Len(Replace(StrEx, SubStr, ""))) / Len(SubStr) 'Result is: (23-17)/2=3 End Sub |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
