Excel - LEFT Function



LEFT Function

The EXCEL LEFT function retrieves the text value from the targeted string depending on the number of characters willing to populate from the front end. The LEFT function does not rely on the preferred language setting, as it computes each character as a single byte. It is merely contrary to the RIGHT function. In simple words, single or more than one character will be returned through this prominent function.

Compatibility

This prominent Excel function is compatible with the following versions of MS-Excel −

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
  • Excel 2024
  • Excel 2024 for Mac
  • Excel 2021
  • Excel 2021 for Mac
  • Excel 2019
  • Excel 2016

Syntax

The syntax of LEFT function is as follows −

LEFT(text, [num_chars])

Arguments

You can use the following arguments with the LEFT function −

Argument Description Required/ Optional
Text It indicates a text string to specific characters from the left side. Required
Num_chars A numeric value that tells you how many characters are to be retrieved from the input string. Optional

Points To Remember

  • If you skip the value of "Num_chars", it is assumed to be one by default, meaning the LEFT function will populate only the first character from the beginning of the input string.
  • If the second argument, "Num_chars", is less than 0, then the LEFT function will retrieve the #VALUE! error.
  • If the "Num_chars" is larger than the total number of input string's characters, then the LEFT function will retrieve the exact input string.
  • Wild cards are not supported in the LEFT function.
  • Unlike other functions, the LEFT function ignores the case differences.

How to use the LEFT Function in Excel?

Extensive step-by-step instructions to utilize the LEFT function are given below.

  • Open the designated Excel worksheet.
  • Select the specific cell and type the = sign, then type the LEFT("text"/cell reference/cell range, num_chars). The text string must be enclosed in the double quotes.
  • Press the Enter tab to obtain the final value.

Examples of LEFT Functions

Practice the following example to learn the use of the LEFT function in Excel.

Example 1: How to Execute the LEFT Function in Excel?

The LEFT function is used to extract a specified number of characters from the beginning of a text string.

Solution

Step 1 − Consider the sample worksheet where the subscription ID is specified in the B column as a combination of characters and numbers. The task is to apply the LEFT function in the C column to separate the ID number (first three numbers) from the 8-digit subscription ID.

How to Execute the LEFT Function in Excel1

Step 2 − Select the range C3:C10 and write the formula =LEFT(B3,3) in the formula box.

How to Execute the LEFT Function in Excel2

Step 3 − After that, press the "ctrl+Enter" to get the computed value in the range C3:C10.

How to Execute the LEFT Function in Excel3

Therefore, the LEFT function retrieved the first three characters from the beginning of the subscription IDs.

Example 2

If the second argument, "Num_chars", is less than 0, then the LEFT function will retrieve #VALUE! error.

Solution

Type the formula =LEFT("Clipboard",-2) in the B2 cell and press the Enter tab. In this formula, the Num_chars contains the negative value that hinders executing the LEFT function.

LEFT Function Will Retrieve VALUE Error1

Therefore, the LEFT function retrieves the #VALUE! Error.

LEFT Function Will Retrieve VALUE Error2

Example 3: Using LEFT and SEARCH Function

The LEFT and SEARCH functions are often used together in Excel (or Google Sheets) to get specific parts of a text from a longer string.

Solution

Step 1 − Suppose the list of last and first names is specified in the B column, such that "Smith" specifies the last name and "John" is a customer's first name.

Is there any exciting way to populate only the last name of the customers?

Yes, we can do this by merging the SEARCH function with the LEFT function.

Using the LEFT and SEARCH Function1

Step 2 − Afterward, select the range C2:C5 and enter the formula =LEFT(B2,SEARCH(" ",B2)-1) in the formula box. In this formula, the SEARCH function extracts the index position of the " " space, and the LEFT function retrieves the text from that space.

Using the LEFT and SEARCH Function2

Step 3 − Finally, press the "ctrl+Enter" to obtain the last name of all customers.

Using the LEFT and SEARCH Function3

Download Practice Sheet

You can download and use the sample data sheet to practice the LEFT function.

Advertisements