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!

4 ★ | 1 Vote

May be interested

  • IRR function in Excel - Usage and examplesIRR function in Excel - Usage and examples
    in 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)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 function to use and specific examples
    vlookup 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 functionHow to combine Vlookup function with Left function
    when 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?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 examplesExcel date function - Usage and examples
    the 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, 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
    dsum 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 examplesPMT function in Excel - Usage and examples
    the 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 examplesHow to use the MAXA function in Excel, detailed examples
    the 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.