Vba Len Function How To Use Len To Find Length Of String In Excel

VBA LEN Function

THE VBA LEN function returns the “length of the string” and the number of characters in the supplied value. Of all the string functions in VBAString Functions In VBAString functions in VBA do not replace the string; instead, this function creates a new string. There are numerous string functions in VBA, all of which are classified as string or text functions.read more, “LEN” is the most under-utilized function. Therefore, we have seen the “VBA LEN” function used as the support function for other string functions like VBA MID FunctionsVBA MID FunctionsVBA MID function extracts the middle values from the supplied sentence or word. It is categorized under the String and Text function and is a worksheet function.read more and VBA RIGHT functionVBA RIGHT FunctionThe Right Function returns the substring from a given string, but the search is done from right to left. This is a type of string function in VBA used with application. worksheet function method.read more.

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

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA LEN (wallstreetmojo.com)

The formula for VBA LEN Function

The LEN function has only one syntax: Expression.

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 VBAExamples Of VBAHere’s a list of the top examples of VBA Macro code in Excel: Print All Sheet Names, Insert Different Color Index in VBA, Insert Worksheets as Much as You want, Insert Blank Row After Every Other Row Highlight Spelling Mistakes.read more length of string function.

Example #1

The length of a VBA string function is very simple to use. For example, look at the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more below.

Code:

Sub LEN_Example()

Dim Total_Length As String

Total_Length = Len("Excel VBA")

MsgBox Total_Length

End Sub

In the above code variable is “Total_Length.”

Dim Total_Length As String

We have assigned this variable the value through the VBA LEN function.

Total_Length = Len(“Excel VBA”)

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

Next, we are showing the result in the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

MsgBox Total_Length

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

VBA LEN as Support Function

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

For example, look at the below sample data.

From the above data, we need to extract dates and remarks separately. Please copy the above data to your Excel sheet and paste it into cell A1.

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

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

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

Example #2

Now, we will show you how to use the VBA length of string as a support function to extract the last name of the full name with the RIGHT and Instr function.

For the demonstration, look at the below data.

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.

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

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

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

Recommended Articles

This article is a guide to VBA LEN Function. Here, we learn how to use the 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 IIFVBA Find and Replace FunctionLeft Function in VBAExcel VBA For Next Loop