What does the left function do in Excel
As its name suggests, the LEFT function in Excel allows you to extract a certain number of characters starting at the left side of a text string. It is basically used for manipulating text data in data analysis.
Syntax
=LEFT(text, [num_chars])
Arguments of Excel Left Function
- text – The text from which to extract characters.
- num_chars – [optional] The number of characters to extract, starting on the left side of the text. By default, it extracts 1 character. If by any chance you put num_chars more than the actually available characters then it’ll extract the entire string.
How to use LEFT function in Excel – formula examples
Here I am going to explain the usage of LEFT function in three different real-life situation basis on the following example :
Examples with explanations using highlighted images
1. How to simply extract a certain number of character
For example, if you want to extract the first 4 characters from the GSTN code mentioned in Cell A1 in the example above use the following formula:
Type =LEFT(A2,2) press ENTER Key and the result is 19
So the formula actually extracts two characters (2) from the code mentioned in cell A2. In the same way, if you want to extract 4 characters from cell A1, you need to type
=LEFT(A2,4) and the output will be 19AA
TIP 1: LEFT belongs to the category of Text functions, therefore the result of a Left formula is always a text string, even if the original value from which you extract characters is a numeric value.
2. How to extract a substring before a certain character using LEFT and FIND functions in Excel
In the previous example, you notice that the GSTN numbers are 15 characters long and the leftmost 2 characters are state code, so I have simply put 2 into the formula to extract state code. But In some cases, you may need to extract the part of the text string where the entire string is not the same character long. So you cannot simply input a predefined number to the num_chars argument of your Left formula as I did in the previous example.
Now, how to do that! See this example here:
See, in the above example, I have used two functions i,e Excel LEFT function, and the Excel FIND function into one formula because as the example shows the names in column A are in different lengths of characters, therefore if you need to extract first name then you can not put a fixed number to the num_chars argument.
So, what should you do? You need to identify the position of the space character between the first name and last name. To identify the position of any character within a text string Excel function Find is used.
In short, the formula shows that all the characters are extracted after identifying the space from the left of the string.
3. How to remove the last few characters from a text string
Sometimes you need to extract the first few characters from a text string by eliminating the last one or two characters which means removing a certain number of characters from the end of the string and taking the rest of the string into another cell.
Let’s look at the example here
Suppose you need to extract the product type into cell B2 from the product code in cell A2. So you need to use Excel Left function in combination with Excel len function.
Now the question is what does the len function do in Excel? In short, the LEN function gets the total number of characters in a string.
In the above example what I actually do is, extract the product type from the product code by eliminating the last few characters from the string. Now, the point is how many characters from last?
Here you need to apply a trick i,e you need to identify if is there any sequence of characters that are the same in all the rows. As you see in the example above product code minus 7 characters equals product type, here in the above formula LEN(A2)-7 does exactly the same work.
Hope I have explained the use of the LEFT function in Excel as easily as you can understand.
Now you need to practice as much as you can to master it.