Excel VBA Left
Some of the excel functionsThe Excel FunctionsExcel functions help the users to save time and maintain extensive worksheets. There are 100+ excel functions categorized as financial, logical, text, date and time, Lookup & Reference, Math, Statistical and Information functions.read more 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 the excel LEFT functionExcel LEFT FunctionThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( “ANAND”,2), the result will be AN.read more, then the VBA LEFT function is the same. It can extract the characters from the left side of the string or the value given by the user.
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 Left Function (wallstreetmojo.com)
The syntax of the LEFT function is the same as in the worksheet function.
It has two arguments.
- String: This is nothing but the value or cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. From this value, we are looking to chop the characters.Length: How many characters do you want to extract from the supplied string? It should be a numerical value.
How to use Excel VBA Left Function?
Example #1
Assume you have the word “Sachin Tendulkar,” and you want only the first 6 characters from this word. We will show how to extract from the left using the LEFT function in VBA.
Step 1: Create a macro name and define the variable as a string.
Code:
Sub Left_Example1()
Dim MyValue As String
End Sub
Step 2: Now, assign a value to this variable.
Sub Left_Example1()
Dim MyValue As String
MyValue =
End Sub
Step 3: Open the LEFT function.
Sub Left_Example1()
Dim MyValue As String
MyValue = Left(
End Sub
Step 4: The first argument is to tell what is the string or value. Our value here is “Sachin Tendulkar.”
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.
Sub Left_Example1()
Dim MyValue As String
MyValue = Left("Sachin Tendulkar", 6)
End Sub
Step 6: Show the value in the VBA MsgBoxVBA MsgBoxVBA 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.
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. Then, we need to add the cell reference and variable value.
Sub Left_Example1()
Dim MyValue As String
MyValue = Left("Sachin Tendulkar", 6)
Range("A1").Value = MyValue
End Sub
We will get the value in cell A1 if you run this code.
Example #2 – LEFT with Other Functions
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. For example, assume the list of values you have in your Excel sheet below.
In each case, first-name characters are different. Therefore, we cannot directly specify the number of characters we need from each name.
That is where the beauty of other functions will come into the picture. For example, we can use the “VBA Instr” function to dynamically supply the number of characters.
In the above names, we need all the characters from the left until we reach the space character. So, the Instr function can return those many characters.
Step 1: Start an excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more name and define a variable as a string.
Sub Left_Example2()
Dim FirstName As String
End Sub
Step 2: Assign the value to the variable through the LEFT function.
Sub Left_Example2()
Dim FirstName As String
FirstName = Left(
End Sub
Step 3: Here, we need to refer to the cell to get the value from the cells. So write the code as CELLE (2,1).Value.
Sub Left_Example2()
Dim FirstName As String
FirstName = Left(Cells(2,1).Value,
End Sub
Step 4: The next thing is how many characters we need. After applying the LEFT function, do not manually enter the characters’ length. Apply the Instr function.
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.
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 one name, but we also have several others. So, if we cannot write 100 lines of code to extract, then how do we extract?
It is where the beauty of loops comes into the picture. Below is the loop code, which can eliminate all the unwanted steps and do the job in 3 lines.
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
If you run this code, we will get the first name values.
Things to Remember
- We can extract the LEFT function only from the left.VBA Instr function finds the position of the supplied character in the string.
Recommended Articles
This article has been a guide to VBA Left Function. Here, we learn how to use the Left function to extract the characters from the left side of the string, along with some examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –
- Select a Range in VBAExcel VBA FunctionsVBA DIR FunctionVBA Select Case
