# How to Find String Length with VBA in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

However, I’ll show you how you can calculate the length of a string with VBA in Excel. Fortunately, you’ll learn to develop a Macro and a user-defined function to complete the task. In this article, I will show you 2 easy ways to find string length with VBA in Excel. Hence, read through the article to learn more and save time.

Here, I have used a VBA code to generate the user-defined LENGTH function to check the string length in Excel. Read More: VBA to Check If String Contains Another String in Excel (6 Methods)

## 2 Easy Methods to Count Length of String with VBA in Excel

Here, we’ve got a data set with the Names and Student IDs of some students of a school called Sunflower Kindergarten. Our objective today is to find out the lengths of the Student IDs with the help of VBA.

### 1. Develop Macro to Count Length of String with VBA in Excel

However, you can develop a Macro to find out the length of a single string or a range of strings using VBA code. Hence, follow the steps below.

Steps:

• Firstly, press ALT+F11 on your keyboard and the VBA window will open. • Secondly, go to the Insert tab in the VBA window.
• Thirdly, from the options available, select Module. • Fourthly, a new module window called “Module 1” will open.
• Then, insert the given VBA code in the opened Module.
``````Sub String_Length()
Dim Length As String
Length = ""
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Columns.Count
Length = Length + Str(Len(Selection.Cells(i, j))) + vbNewLine
Next j
Next i
MsgBox Length
End Sub`````` Explanation of the Code:

• First, Sub String_Length() declares the starting of the Macro String_Length.
• Then, Dim Length As String declares a string called Length.
• Next, Length = “” sets the initial value of the string Length to a blank character.
• For i = 1 To Selection.Rows.Count starts an iteration for each row of the selected range.
• For j = 1 To Selection.Columns.Count starts an iteration for each column of the selected range.
• Thereafter, Length = Length + Str(Len(Selection.Cells(i, j))) + vbNewLine accumulates the lengths of all the strings one by one. This is the most crucial line.
• Next j declares the end of the 2nd for-loop.
• Next i declares the end of the 1st for-loop.
• Now, MsgBox Length shows all the lengths in a message box.
• Lastly, End Sub declares the end of the Macro.
• Next, come back to your worksheet.
• After that, select the range of cells whose length you want to find out. Here, I have selected the column Student ID. Finally, hit the F5 key and you will find a message box showing the lengths of all your selected strings one by one. ### 2. Run User-Defined Function to Calculate Length of String with VBA in Excel

Last but not least, you can also develop a user-defined function to calculate the length of a single string or a range of strings using VBA code in Excel. Hence, follow the steps below.

Steps:

• Initially, accomplish steps 1-4 from the previous method to open a new module, and insert the VBA code.
``````Function LENGTH(Str As Variant)
Dim Output() As Integer
ReDim Output(Str.Rows.Count - 1, Str.Columns.Count - 1)
For i = 0 To Str.Rows.Count - 1
For j = 0 To Str.Columns.Count - 1
Output(i, j) = Len(Str(i + 1, j + 1))
Next j
Next i
LENGTH = Output
End Function`````` Explanation of the Code:

• Firstly, Function LENGTH(Str As Variant) starts a new user-defined function called LENGTH that takes a range of cells known as Str as input.
• Secondly, Dim Output() As Integer declares an array of integers known as Output.
• Thirdly, ReDim Output(Str.Rows.Count – 1, Str.Columns.Count – 1) sets the new dimension of the range Output equal to the input range Str.
• For i = 0 To Str.Rows.Count – 1 starts an iteration for each row of the input range Str.
• For j = 0 To Str.Columns.Count – 1 starts an iteration for each column of the input range Str.
• After that, Output(i, j) = Len(Str(i + 1, j + 1))sets the values of the array output to the lengths of the array Str.
• Next j ends the 2nd for-loop.
• Next i ends the 1st for-loop.
• LENGTH = Output sets the array Output as the output of the function.
• Finally, End Functiondeclares the end of the function.
• Then, come back to your worksheet.
• Next, select any cell and insert the formula mentioned below. Here I want to know the lengths of the cells of the columns Student ID.
`=LENGTH(C4:C13)` Note: This is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.

• Afterwards, you will get the lengths of all the cells arranged beautifully in a new range. ## Conclusion

Using these methods, you can calculate the length of a single string or a range of strings with VBA in Excel. Do you have any questions? Feel free to ask us.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags: Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  