This blog covers one of the most used formulas in Excel viz. the excel LEFT function. The LEFT function, as the name itself suggests, is used to extract a certain number of characters from the beginning or start of a text in excel.
This tutorial covers purpose of LEFT function, its syntax, arguments, and how to use LEFT function in excel with the help of examples.
Let’s begin. Here we go 😎
When to Use LEFT Excel Function
The LEFT excel formula is useful to separate or extract the specific number of characters from the left side of a text.
This function returns a text string as its output.
Syntax and Arguments
=LEFT(text, [num_chars])
Below bullet points explain the two arguments of the LEFT formula in excel.
- text – In this argument, specify the text from which you want to extract the sub-string. It can be either manually entered or as a reference to another cell.
- [num_chars] – In this optional argument, specify the number of characters to extract from the left.
If you keep the [num_chars] argument as blank, then it will take ‘1’ as by default. In this case, the LEFT formula will extract first character from a text.
Examples of LEFT Function in Excel
In this section, we would learn about how does the LEFT function work in Excel with the help of some examples.
Ex. 1 # Extract First Character from a Cell or Text in Excel
The below image shows a list of material codes wherein the first letter denotes the batch number of that material.
To get the first character (batch number) from every cell, use the following formula in cell B2:
=LEFT(A2)
As a result, excel would return the first character (viz. batch code) in column B, as shown in the image below:
Explanation – In the above example, since we have not used the [num_chars] argument, excel by default extracts and returns the first character.
Ex. 2 – Extract Specific Number of Characters from the Left of the Text
Let’s say that you want to extract a specific number of characters from the left of the cell.
In that scenario, use the [num_chars] argument as explained via below example.
Suppose you want to extract the first three characters from each of the above-listed material codes. To achieve this, simply enter the value 3 as [num_chars] argument.
=LEFT(A2,3)
Ex. 3 – Extract Text Before a Particular Text or Character
The above learned two examples were basic examples to understand how the LEFT formula functions in excel.
However, many times you may not know what should be the exact [num_chars] value, because it may differ in each of the scenarios.
To best understand such a scenario, let us take the below example:
In the above example, the alphabet portion represents batch number and the number portion represents material.
Since for each of the material codes, the length of the batch number varies, it is impossible to use a fixed [num_chars] value.
In that case, to extract the text from the left before the first space, you need to use the LEFT function in conjuction with FIND or SEARCH excel formula.
=LEFT(A2,FIND(" ",A2)-1)
OR
=LEFT(A2,SEARCH(" ",A2)-1)
Explanation – In the above example, the FIND and the SEARCH function searches for the space character, and returns its position.
This method is widely used to extract the first name from the full name in excel. Check our blog post on how to separate the first name from the full name in excel.
The LEFT function belongs to the text function group. Therefore, it returns the value from the left of the string, and stores it in the text format, irrespective of the original number. Check the below section.
How to make LEFT Function Return A Number?
As mentioned above, the LEFT function, being a text function returns a text string and not a number. This means that a number output of the LEFT function can not be used to perform any calculation, because it is a text string.
To let the LEFT formula return a number, instead of a text string, simply convert it into value by embedding it into the VALUE function. See below syntax:
=VALUE(LEFT())
Explanation – The VALUE function converts a number string into a proper number. In the above example, the LEFT(A2,3) function extracts the first three numbers from text in cell A2 and then the VALUE function converts that text into a number.
Thank You 🙂