Under TEXT functions, you will find one of the most used LEFT functions. With this function, you get a particular number of characters from the beginning of a given text data. If you need to pull characters from the left side of a text string, the LEFT function is really helpful in this instance. Today, you will get to know how to use LEFT function in Excel. Besides, you will get to know that the LEFT function can be used with SUM, VALUE, DATE, DAY, COUNT, etc functions as well.
LEFT Syntax
=LEFT(text,[num_chars])
Below is the explanation of arguments used in this syntax:
Text: It is a compulsory argument, which is the original text string.
Num_chars: It is an optional argument used for specifying how many numbers are needed from the start of the given text. It also gives how many characters you can extract ranging from the left side of the text.
Usage Notes of LEFT Function in Excel:
- The LEFT function in Excel is used for extracting characters that start from the left side of the text.
- You will use the default to 1 and Number_of_characters is not required.
- With this function, you can extract digits from the numbers.
- In the LEFT function, you cannot extract number formatting because it is not a part of the number.
How to Use LEFT Function in Excel?
Till now, you get to know that the LEFT function can extract text from the left of a string. What else you can do with this function?
Below are some examples for you that explain how you can use left function in Excel when you make a combo with other functions to solve complex tasks.
How to Extract a Substring Before a Certain Character?
Sometimes, you have to extract a part of the text string that leads to a certain character. Suppose, you are required to extract the first name from the column of full names but the thing is each name has a different number of characters. This is why you are not able to provide a preset number to the num_chars argument of the Left formula.
In case, you find a space in the first and last names you often tend to deal with the position of the space character in a string. You can use the SEARCH or FIND function for this. You will see the space returns by using this simple formula when the full name is present in the cell A2:
SEARCH(“”,A2)
The following formula is now set in the num_chars argument of the LEFT syntax:
=LEFT(A2, SEARCH(“”,A2))
By removing the space, you can make the formula better than before and for this, you need to subtract 1 from the Search formula outcome.
=LEFT(A2, SEARCH(“”,A2)-1)
Similarly, you can pull the names from the column. For this, you can use the Search function:
=LEFT(A2, SEARCH(“-“, A2)-1)
The below-given formula can be used to subtract the earlier characters:
LEFT(string, SEARCH(character, string) -1)
Examples: How to Use LEFT Function in Excel
Worksheet Function
Below we have some examples for a better understanding of the LEFT function in Excel. With these examples, you can have an in-depth understanding.
The LEFT formula in the sheet will be as:
=LEFT(A1, 5)
TOMAT is the outcome based on the syntax of the LEFT function.
LEFT Function Example #1
The LEFT formula in Excel can be written as:
=LEFT(A2, 8)
Outcome: amazon.c
For this, below is a sheet given to you.
LEFT Function Example #2
When the LEFT formula is written as:
=LEFT(“Excel”,2)
Outcome: “Ex”
Below is a sheet to consider.
LEFT Function Example #3
In this example, we will use the data given below:
- 85522-10002
- 91-98125-55237
- Original Text
The above data has the return values for the Excel LEFT function mentioned.
When the LEFT formula in Excel is added as:
=LEFT(A7,4)
Outcome: 8552
When the LEFT formula in Excel is added as:
=LEFT(A8, 5)
Outcome: 91-98
When the LEFT formula in Excel is added as:
=LEFT(A9,5)
Outcome: Origi
Summary:
In this post, you have learned how to use LEFT function in Excel. With the examples given above, your understanding of the LEFT function improves better than before.
