Learn about VBA Left Function

0
78

Learning about VBA Left

In this Article, we will understand about following topics

  1. Introductin of VBA Left
  2. VBA Code for Basic understanding of Left Function
  3. VBA Code for Extract All Characters
  4. VBA Code for Dynamic Length
  5. VBA Code use with Len Function
  6. VBA Code for Conditional logic

Introduction

The Left function in VBA is used to extract a specified number of characters from the start (left side) of a string. It is useful when you need to work with a substring or truncate data from the beginning of a string.

Syntax

Left(string, length)

Parameters

  1. string (Required):
    The input string from which characters will be extracted.
  2. length (Required):
    The number of characters to extract from the left side of the string.

    • Must be a positive integer.
    • If length is greater than the total characters in the string, the entire string is returned.

Return Value

  • Returns a string containing the specified number of characters from the start of the input string.

Example:

  1. Basic Example

Extract data the first 5 character from the string:

VBA Code

Sub leftexample()

Dim text As String

Dim result As String

text = "Hello World"

result = Left(text, 5)

MsgBox result

End Sub

2. Extract All Characters If Length Exceeds String Length

If the length exceeds the string’s total length, the entire string is returned:

VBA Code:

Sub leftexample2()

Dim text As String

Dim result As String

text = "Hello World"

result = Left(text, 20)

MsgBox result

'here result will be "Hello World"




End Sub

3. Dynamic Length Example

Use a variable for the number of characters to extract:

VBA Code:

Sub DynamicLength()

Dim text As String

Dim charCount As Integer

Dim result As String




text = "Programming"

charCount = 5

result = Left(text, charCount)




MsgBox result ' Output: "Progr"

End Sub

4. Use with Len Function

Extract half the characters from a string:

VBA Code:

Sub HalfString()

Dim text As String

Dim halfLength As Integer

Dim result As String




text = "Visual Basic for Applications"

halfLength = Len(text) \ 2 ' Integer division to get half

result = Left(text, halfLength)




MsgBox result ' Output: "Visual Basic f"

End Sub

5. Use in Conditional Logic

Check the starting characters of a string:

VBA Code:

Sub checklist1()

Dim text As String

text = "VBA is awesome"

If Left(text, 3) = "VBA" Then

MsgBox "The String Starts with VBA"

Else

MsgBox "The String does not start with VBA"

End If

End Sub

VBA Code Excel File for Download

 

Practical Applications

  1. Extracting Parts of Names:

Extract the first name from a full name stored in a single string.

2. File Name Manipulations:

Extract the first few characters of a file name for validation or renaming purposes.

3. Data Cleaning:

Trim prefixes or unwanted text from strings in a dataset.

Related article to read 

  1. VBA Variable : In VBA (Visual Basic for Applications), variables are used to store data that your code can manipulate. Here’s a quick rundown of how to declare and use variables in VBA

2. VBA If Then Else : The If…Then…Else statement in VBA is used to execute different blocks of code based on whether a condition is True or False

3. VBA msgbox : In VBA (Visual Basic for Applications), the MsgBox function is used to display a message box to the user

4. VBA Error Handling : In VBA (Visual Basic for Applications), error handling allows you to gracefully handle runtime errors that occur while the program is running.

LEAVE A REPLY

Please enter your comment!
Please enter your name here