Excel LEFT Function (Table of Contents)
LEFT Function in Excel
There are many functions which are categorized under TEXT category in the Formula tab of the Microsoft Excel. LEFT is also one of the functions which are under head TEXT. It is widely used where we have to extract a few letters or word as a result of output.
As we can see in the above screenshot that the LEFT function is where located. After putting cursor, itself, it defines as it returns the specified number of characters from the start of a text string. This function helps us to extract the substring from a string which starts from the very leftmost character.
This function can also be used with the help of VBA which can be entered through the Microsoft Visual Basic Editor. A LEFT function can also be used by combining it with Excel functions like SUM, COUNT, DATE, VALUE, etc.
LEFT Formula in Excel
Below is the LEFT Formula in excel:
The LEFT Function uses the following arguments:
- TEXT: (required/mandatory argument)
It is the original text string or character from which we have to extract the substring or characters.
- NUM_CHARS: (Optional Argument)
It is the optional argument which refers or defines the number of characters which we wish to extract from the string or text. The default is 1. It starts with the leftmost character of the string or text if this is omitted then only one character will be returned.
Also, if the NUM_CHARS is greater than the character of a word then it would result as a whole string or word.
Result/Return Value:
After applying LEFT, the result will be either a string or letter or whole text depends on the requirement.
The types of LEFT function (where we can use) are the following:
- VBA Function (VBA)
- Worksheet Function
How to Use the LEFT Function in Excel?
LEFT Function in Excel is very simple easy to use. Let us now see how to the use LEFT function in Excel with the help of some examples.
Example #1
Firstly, we will see the example of a LEFT function without an optional argument to make it clear of usage:
Step 1: We have to start the formula from “ =“, then type LEFT and then tab. The picture of Step 1 is below:
Step 2: We can see in the above pic that it is asking for text, it means the word or string for which we have extracted the string, so we can use the cursor or put the address of the cell, manually as well. Refer to the pic below:
So, in the above picture, we are using the LEFT function for the cell address A5 which is written as Function.
Step 3: As we do not have to use the optional argument in this example, we have to just click enter or we can close the bracket or press enter. Both will have the same result.
We can see from the above picture that the result or output of the Formula is “F “ . As we noted earlier in this article that if the optional argument whose syntax is num_chars, is not given then it will automatically come as default 1. Hence the result is F, which is the first character of the Function from the left.
Example #2
Now, we will see the other scenario where the optional argument is greater than the number of words, then it will return as a whole string or word.
As we can see from the above example that the optional argument which is num_chars is written as 5, however, the example Eye is only of three characters. Hence the result is the whole string which is Eye.
Example #3
Now, we will see the example with the optional argument (num_chars). How we can use it?
Suppose, in a report, we have to give only the initial of a given name, then in this scenario, the LEFT function will help us.
Step 1: We will apply the LEFT formula in cell B4.
Step 2: Now we have to enter the syntax, cell address which is A4 for which we have to extract the substring. Like the below picture, we have to put a comma and then the number of characters which we want to extract. So, in this example we want initial so take it as 1.
Take the reference of below picture:
Step 3: Just close bracket to complete the syntax or just press Enter. The result would R which is an initial or first character of Ram in the example. Please refer below pic:
We can drag the formula by using Ctrl + D or double click on the right corner of the cell as shown below in red:
The result would be like below:
So, instead of doing it manually we can use the function of the LEFT which helps in time-saving because it will take lots of time if the data is huge.
Example #4
Suppose, in data numbers and text are mixed and we have to separate it. Then, in this case, the LEFT formula will help with the combination of the VALUE function.
Below is the example is given where 3 character is numeric, which we have to extract from a name.
Now, we have to use the Formula with VALUE.
Suppose, we wish to calculate in column D. So, the Formula would be:
=VALUE(LEFT(B3,3))
From the above formula, we can understand that B3 is cell address from which we have to extract the string and 3 is the character which we want.
Below is the picture with the formula:
Now we can see the result after application of this LEFT function with the combination of VALUE. Refer Below picture:
Hence, we can see that the result is 123 of cell address D3 because, after the application of the function, it extracts the three value from cell B3 from the leftmost i.e. 123. Simultaneously, we can use this function for all the below cell which could save lots of time.
Example #5
The LEFT Function can be used with the date as well. But the only condition is that it should be in number format, not in the text.
We can refer the below pic for more clarification:
We can see the formula in cell address E4,
The result is correct which is 20 after application of LEFT Function with num_chars as 2,
Whereas in E5, the same formula is used.
But the result is 62 which not correct because D5 is in Text Format, not in number format like D4.
LEFT Function in Excel VBA
The LEFT Function which we have seen above is used in Worksheet.
Now, we will see in the VBA code in MS Excel.
Below is the formula or code which is to be used in VBA, shown as an example of Ball, so the result would B because the num_chars is defined as 1 in below example.
Things to Remember About LEFT Function in Excel
- The result or return value depends on the arguments which are inserted in the syntax.
- If the argument is less than (num_chars) 0, then the result would be a #VALUE error.
- The output or return value can be string or text.
- The LEFT function is used for extracting a substring from the string, which calculates from the leftmost character.
Recommended Articles
This has been a guide to LEFT in Excel. Here we discuss the LEFT Formula and how to use the LEFT function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
