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.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 1

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:

1. text is a required argument, this is the text string or cell reference to the text string containing the characters you want to extract.
2. 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.
3. Num_chars must be greater than or equal to zero, if num_chars <0 , the function will return the #VALUE! Error.
4. If Num_chars is larger than the length of the text, the LEFT function will return the entire text.
5. 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.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 2

Or reference to the cell containing the text string.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 3

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.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 4

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.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 5

Then copy the formula down to the cells below.

Example 3: Using the VLOOKUP and LEFT functions

Suppose you have the following data:

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 6

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:

1. Function LEFT (C7; 2) returns the first two characters in which employees , this will be the value to be detected in VLOOKUP.
2. $ 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.
3. 2 is the return column, which is the job column in the Jobs table .
4. 0 is the correct detection type.

Your results will be as follows:

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 7

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.

LEFT function in Excel, how to use LEFT function and illustrative examples Picture 8

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!