Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

## VBA Left Function

Some of the excel functions are integrated with VBA as well. Of all the many text functions, VBA LEFT is one of those functions we use quite often than other formulas.

If you know excel LEFT function, then VBA LEFT function is exactly the same. The LEFT function can extract the characters from the left side of the string or value given by the user.

I would say we can chop the characters from the supplied value from left side based on the number of characters given by the user.

### Formula

The Formula of the LEFT function is exactly the same as in the worksheet function.

It has two arguments.

**String:**This nothing but the value or cell reference. From this value, we are looking to chop the characters.**Length:**How many characters you want to extract from the**string**you have supplied. This should be a numerical value.

VBA LEFT is a part of text functions, other important text functions are RIGHT, LEN, MID, and TEXT functions.

### Examples of Excel VBA Left Function

Following are the examples of VBA Left Function.

Assume you have a word “Sachin Tendulkar” and you want to only the first 6 characters from this word. We will show how to extract from left using the LEFT function in VBA.

**Step 1: **Create a macro name and Define variable as String.

**Code:**

Sub Left_Example1() Dim MyValue As String End Sub

**Step 2: **Now assign a value to this variable

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = End Sub

**Step 3: **Open LEFT function.

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = Left( End Sub

**Step 4: **First argument is to tell what is the string or value. Our value here is “Sachin Tendulkar”.

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", End Sub

**Step 5: **Length is nothing but how many characters we need from the left. We need 6 characters.

4.6 (247 ratings)

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) End Sub

**Step 6: **Show the value in the VBA MsgBox.

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) MsgBox MyValue End Sub

**Step 7: **Run the macro using the F5 key or manually through a run option to get the result in a message box.

**Output:**

Instead of showing the result in a message box, we can store this result in one of the cells in our excel worksheet. We just need to add the cell reference and variable value.

**Code:**

Sub Left_Example1() Dim MyValue As String MyValue = Left("Sachin Tendulkar", 6) Range("A1").Value = MyValue End Sub

Now if you run this code we will get the value in cell A1.

### LEFT with Other VBA Functions

The beauty of any function is when we use them with other functions to make the formula dynamic.

In the above case, we have directly supplied the length of the characters we need from the left direction, but this is more suitable for one or two values. Assume below is the list of values you have in your excel sheet.

In each every case first name characters are different from one to another, we cannot directly specify the number of characters we needed from each name.

This where the beauty of other functions will come into the picture. In order to supply the number of characters dynamically, we can use “VBA Instr” function.

In the above set of names, we need all the characters from the left until we reach space character. So Instr function can return those many characters.

**Step 1: **Similarly start a macro name and define a variable as a string.

**Code:**

Sub Left_Example2() Dim FirstName As String End Sub

**Step 2: **Assign the value to the variable through the LEFT function.

**Code:**

Sub Left_Example2() Dim FirstName As String FirstName = Left( End Sub

**Step 3: **Here we need to refer the cell to get the value from the cells. So write the code as **CELLE (2,1).Value.**

**Code:**

Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2,1).Value, End Sub

**Step 4: **Next thing is how many characters we need. After applying the LEFT function don’t enter the length of the characters manually, Apply the Instr function.

**Code:**

Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2, 1).Value, InStr(1, Cells(2, 1).Value, " ")) End Sub

**Step 5: **Store this result in the B2 cell. So the code is **CELLS (2,2).value = FirstName**

**Code:**

Sub Left_Example2() Dim FirstName As String FirstName = Left(Cells(2, 1).Value, InStr(1, Cells(2, 1).Value, " ")) Cells(2, 2).Value = FirstName End Sub

**Step 6: **Run this code manually or through F5 we will get the first name.

We got the first name for the one name, but we have several other names as well. We cannot write 100 lines of code to extract, then how do we extract?

This is where the beauty of loops comes into the picture. Below is the loop code which can eliminate all the unwanted steps and can do the job in 3 lines.

**Code:**

Sub Left_Example2() Dim FirstName As String Dim i As Integer For i = 2 To 9 FirstName = Left(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ") - 1) Cells(i, 2).Value = FirstName Next i End Sub

**Note:**Instr function returns space character as well, so we need to minus 1 from the Instr result.If you run this code we will get first name values.

**Output:**

### Things to Remember Here about VBA Left Function

- Left can extract only from left.
- VBA Instr function finds the position of the supplied in character in the string.

### Recommended Articles

This has been a guide to VBA Left Function. Here we learned how to use Left Function in Excel VBA to extract the characters from the left side of string along with some simple to advanced examples. Below are some useful excel articles related to VBA –

- Select a Range in Excel VBA
- VBA Functions in Excel
- Formula of VBA DIR Function
- VBA Set with Worksheet Object Variables
- VBA Select Case Example
- VBA UBOUND Function
- VBA GoTo Statement
- Create VBA InputBox

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion