LEFT function in Excel, how to use LEFT function and illustrative examples
LEFT function in Excel is a function of string processing function, you use LEFT function when you need to cut the character string to the left of the text string. If you need to learn more about MID function, please refer to the article below to understand how to use LEFT and illustrative examples.
Below article describes, syntax, examples illustrating how to use LEFT function in Excel, please follow along.
LEFT function description
The LEFT function in Excel returns the first or multiple characters in a string, based on the number of characters you specify, which is often called the string literal function to the left. There is also the RIGHT function that cuts the right string, the MID function takes the middle string.
The syntax of the LEFT function
= LEFT (text; [num_chars])
Inside:
- text is a required argument, this is the text string or cell reference to the text string containing the characters you want to extract.
- num_chars is an optional argument, this is the number of characters you want LEFT to extract starting from the first position on the left of the text.
- Num_chars must be greater than or equal to zero, if num_chars <0 , the function will return the #VALUE! Error.
- If Num_chars is larger than the length of the text, the LEFT function will return the entire text.
- If you omit num_chars , the default num_chars = 1.
LEFT function example
Example 1: Use LEFT function to extract the first 8 characters in the string 'TipsMake.vn'
You can enter the text string directly into the LEFT function.
Or reference to the cell containing the text string.
Example 2: Use the LEFT function in combination with the SEARCH function to extract data in a text string from the first character to the previous character.
Suppose you want to separate your gmail account from gmail with the full format @ gmail.com.vn as shown below.
You do the following:
In the first cell in the column LEFT (D7) you enter the function
= LEFT (C7; SEARCH ("@"; C7) -1)
The Search function will locate the @ special character in the full gmail text string (C7) and return the @ starting position number in the C7 string. The position of the Search function will return the position of the @ character so you need to subtract 1 to not extract the whole @ character. Thus the LEFT function will return the character string from the first character to the position before the @ character in the text string in cell C7.
Then copy the formula down to the cells below.
Example 3: Using the VLOOKUP and LEFT functions
Suppose you have the following data:
Fill in the Jobs column in the Employee List table based on employee code and job title.
Doing:
Get the first 2 characters in the employee code and search for these two characters in the Jobs table to show jobs in the Jobs column of the Employee List table .
In the first cell of the Jobs column , enter the function
= VLOOKUP (LEFT (C7; 2); $ C $ 19: $ D $ 21; 2; 0)
Inside:
- Function LEFT (C7; 2) returns the first two characters in which employees , this will be the value to be detected in VLOOKUP.
- $ C $ 19: $ D $ 21 is the data area in the Jobs table , which is the area to be searched and returns the result of VLOOKUP, because this area is fixed so you select the region C19: D21 and then press the F4 key to fix the region to $ C $ 19: $ D $ 21.
- 2 is the return column, which is the job column in the Jobs table .
- 0 is the correct detection type.
Your results will be as follows:
Then you copy the function formula to the cells below, because the lookup table is the Jobs table that has been fixed, so copy it, the lookup table is still fixed.
Above the article has shared to you the description, syntax and specific examples of LEFT function in Excel. Hopefully through this article, you will understand how to use the LEFT function and apply the LEFT function to specific cases to process data in Excel. Good luck!
You should read it
- Choose function in Excel, how to use the Choose function and illustrative examples
- Do you know the structure and usage of the LEFT function in Excel?
- LEFT function in SQL Server
- Match function in Excel - Usage and illustrative examples
- OR function in Excel, how to use the OR function, and examples
- How to use the FIND function in Excel?
- DCount function in Excel - How to use the DCount function and examples using the DCount function
- INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function
May be interested
- IRR function in Excel - Usage and examplesin excel financial functions, users cannot ignore irr - the function that returns the internal rate of return for a series of cash flows. so how does the function structure and usage of the irr function? please read the article below.
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)the following article dexterity software will introduce readers to the address function one of the most popular reference and lookup functions in excel.
- VLOOKUP function to use and specific examplesvlookup is one of the most useful excel functions but few understand it. in this article, we will help you understand vlookup with practical examples, such as grading students and staff using the vlookup function.
- How to combine Vlookup function with Left functionwhen combining the vlookup function with the left function, we will easily separate the values to find the right information we need.
- Do you know the structure and usage of the LEFT function in Excel?this article guides you in detail about the structure and usage of common and effective left function in excel
- Excel date function - Usage and examplesthe date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
- The MID function in Excel, how to use the MID function, and examplesthe mid function in excel, how to use the mid function, and examples. the mid function in excel is a function of the text function group, you use the mid function if you want to cut the string in the middle of the text string when processing the string. if you do not know or do not understand the mid function, then you
- DSUM function in Excel, how to use DSUM function and examplesdsum function in excel, how to use dsum function and examples. the dsum function in excel is a fairly common function with a fairly simple usage. but if you do not know how to use the dsum function, you can refer to the following article to understand h
- PMT function in Excel - Usage and examplesthe pmt function is one of the built-in financial functions of the excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. the pmt function is not only useful for businesses, but also very practical for users if you want to calculate a loan.
- How to use the MAXA function in Excel, detailed examplesthe maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.