WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA LEN

VBA LEN

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Len function is a common function for both worksheet and VBA, it is an inbuilt function for both of the platforms and the syntax to use this function is also similar, the arguments this function takes in both platforms are similar which is a string and the use or the output for this function is same as it returns the length of a string.

VBA LEN Function

VBA LEN function returns the “length of the string,” i.e., and it returns how many characters are there in the supplied value. Of all the string functions in VBA, “LEN” is the most under-utilized function. I have seen the “VBA LEN” function used as the support function for other string functions like VBA MID Functions and VBA RIGHT function.

How do you find the length of the string or value?

For example, if you the sentence “Hello Guys, Good Morning!!!” and you want to find the number of characters in it, how do you find it? In this article, we will show you the “VBA LEN” function.

VBA LEN

The formula of VBA LEN Function

LEN function has only one syntax, i.e., Expression.

VBA Len Formula

An expression is nothing but the value we are trying to test.

For example, Len (“Good”) will return 4.

Examples

Below are the examples of VBA Length of String Function.

Example #1

The length of a VBA String function is very simple to use. For example, look at the below VBA code.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub LEN_Example()

    Dim Total_Length As String

    Total_Length = Len("Excel VBA")

    MsgBox Total_Length

End Sub

Vba len Example 1

In the above code variable is “Total_Length.”

Dim Total_Length As String

For this variable, we have assigned the value through the VBA LEN function.

Total_Length = Len("Excel VBA")

For the LEN function, we have assigned the value as “Excel VBA.”

Total_Length = Len("Excel VBA")

Next, we are showing the result in the VBA message box.

MsgBox Total_Length

When I run this code using the F5 key or manually, we will get 9 as a result because space is also a character.

Vba len Example 1-1

Vba len Example 1-2

VBA LEN as Support Function

Example #1

The purpose of the LEN function is mostly utilized with other functions. I have used this function with RIGHT & Instr functions.

For example, look at the below sample data.

Vba len Example 2

From the above data, we need to extract Date separately and Remarks Separately. Copy the above data to your excel sheet and paste in Cell A1.

In order to extract these elements, we need to use the LEN function with other string functions. The below code will do the job for us.

Code:

Sub LEN_Example1()

    Dim OurValue As String

    Dim k As Long

    For k = 2 To 6
'In this case my data started from second cell and ends at 6th.
'Based on your data change the numbers

        OurValue = ActiveSheet.Cells(k, 1).Value

       'This will extract first 10 characters i.e. Date portion
        ActiveSheet.Cells(k, 2).Value = Left(Trim(OurValue), 10)

       'This will extract teh remarks portion
        ActiveSheet.Cells(k, 3).Value = Mid(Trim(OurValue), 11, Len(Trim(OurValue)) - 10)

     Next

End Sub

length of string Example 2-1

When we run this code manually or through the F5 key, we will get the result like the below.

Vba len Example 2-2

Example #2

Now I will show you how to use VBA Length of string as a support function to extract the last name of the full name with the RIGHT & Instr function.

For the demonstration, look at the below data.

length of string Example 3

From the above list, we need to extract the Last Name from the full name. The below code will extract the last name. LEN is used as a support function here.

Code:

Sub LEN_Example2()

    Dim FullName As String

    Dim k As Long

    For k = 2 To 8

        FullName = ActiveSheet.Cells(k, 1).Value

        'This will extract last name
       ActiveSheet.Cells(k, 2).Value = Right(FullName, Len(FullName) - InStr(1, FullName, " "))
        'LEN finds full number of characters
        'Instr finds space character
        'LEN - Inst will gives total characters from the right
     Next

End Sub

length of string Example 2-4

Run the code using the F5 key, or you can run manually and see the result.

Vba len Example 2-4

You can download this Excel VBA Length of String here – VBA LEN Function Template

Recommended Articles

This has been a guide to VBA LEN Function. Here we learn how to use LEN Function in VBA to find the length of the string along with practical examples and downloadable codes. Below are some useful excel articles related to VBA –

  • VBA IIF
  • VBA Find and Replace Function
  • Left Function in VBA
  • Excel VBA For Next Loop
8 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Coursera IPO Financial Model & Valuation Free Download