LEFT Function in Excel

What Is LEFT Function In Excel?

The LEFT Function in Excel is a text function that returns the number of characters from the beginning of the string, i.e., from the left. The LEFT function is an inbuilt function in Excel. It is part of the Excel Text Functions in the Formulas tab.

For example, consider cell A1 with the text string Peter. Now, let us understand how LEFT Function in Excel works with the following steps.

Left Function in Excel Intro

The steps required to use LEFT function in excel are as follows:

Step 1: First, select the cell where we want to display the result. In this case, select cell B1.

Step 2: Next, type the LEFT Function in cell B1.

The formula is =LEFT(A1).

Step 3: Then, press Enter key.

Excel Intro Example

We can see that the function has returned the output as ‘P,’ which is the first character of the entered string while taken from the left.

Thus, we can easily use the LEFT function in excel to find values.

Key Takeaways
  • LEFT Function in Excel is a function that fetches the substring from a string, starting from the left side.
  • The formula of LEFT function in excel is =LEFT(text,[num_char) where,
    • text is a mandatory argument that denotes the text string from which we fetch the characters.
    • num_chars is an optional argument that indicates the number of characters.
  • The #VALUE! error occurs if the given value to the num_char argument is not 1 or less than 0.
  • If there are any extra spaces in the formula, the obtained value might have an error or the result might be incorrect.

LEFT( ) Excel Formula

The syntax of the LEFT Function in Excel, along with the arguments, are as follows:

Left Function

where,

  • text: It is a mandatory argument and denotes the text string from which we fetch the characters.
  • num_chars: It is an optional argument indicating the number of characters we want to fetch.

How To Use LEFT( ) In Excel?

Let us understand the two ways to use LEFT function in Excel.

1. Access Left Function from the Excel ribbon

  1. Go to the Formulas

  2. Click on the Text option from the Function Library group


    Left Function in Excel.1

  3. Choose the LEFT function from the drop-down list.


    Left Function in Excel.2

  4. A window called Function Arguments pops up.

  5. Enter the arguments in the Text & Num_chars boxes.


    Left Function in Excel.3

  6. Click OK.

2. Enter the worksheet manually

  • Select an empty cell for the output.
  • Type =LEFT( in the selected cell.

Alternatively, type =L and double-click the LEFT Function from the list of suggestions shown by Excel.

Left Function in Excel.4
  • Enter the arguments as cell references or direct values.
  • Close the parenthesis and press the Enter key.

For example, the below image shows text string values in cell A2. We need to use the following steps to understand LEFT Function in Excel.

Basic Example

The steps used to find values using LEFT Function in excel are as follows:

Step 1:  To begin with, select an empty cell for the output. We have selected cell B2 in this case.

Basic Example.1

Step 2:  Next, start by entering the formula in cell B2.

Step 3: Then, we should select the cell having the text. So, in this example, the selected cell is A2.

The complete formula is =LEFT(A2).

Basic Example.2

Step 4: Press Enter key.

We can see that the function has returned the value ‘A’ in cell B2, which is the first character from the left side of the string.

Left Function in Excel Basic Example.3

Thus, the LEFT function returns the values in excel.

Examples

Example #1 – Extracting A Substring Before A Certain Character

The below table shows the first names of people in column A. Now, we need to use the following steps to learn how to use left function in excel with the SEARCH function to fetch a substring before space between the two strings.

Excel Example 1

The steps to demonstrate the LEFT Function are as follows:

Step 1:  First, select an empty cell for the output. We have selected cell B2 in this case.

Step 2: Next, start by entering the formula in cell B2.

Step 3: Select the cell reference with the cell having the text string.

So, in this example, select cell range A2:A3.

Example 1.1

Step 4: Enter the SEARCH formula with a blank space.

Excel Example 1.2

So, the complete formula is =LEFT(A2:A3,SEARCH(“ ”,A2:A3)).

Excel Example 1.3

Step 5: Press Enter key.

Left Function in Excel Example 1.4

We can see that the first name, ‘John’ is displayed in cell B2, i.e., the first name from the left side of the string before the space is fetched as output.

Step 6: Simply drag the arrow key to cell A3 to display the result in B3.

Excel Example 1.5

Likewise, we can use LEFT function to obtain output in Excel.

Example #2 – Removing the last N characters from a String

The below table shows the text string in cell A2. We need to use the below steps to learn how to use left function in excel with the LEN function to remove the last specified number of characters from the string.

Left Function in Excel Example 2

The steps to use the LEFT Function are as follows:

Step 1:  To begin with, we should select an empty cell to display the output.

In our example, let us select cell B2.

Excel Example 2.1

Step 2: Next, enter the formula in cell B2.

Excel Example 2.2

Step 3: Now, select the cell having the text string. In this example, the selected cell is A2.

Excel Example 2.3

Step 4: Enter the LEN formula and the cell reference whose length is checked. Again, in this example, the selected cell is A2.

Example 2.4

Step 5: Now, we need to enter the number of characters we want to eliminate from the string. Here, ‘9’ will be the num_char value.

So, the complete formula is =LEFT(A2,LEN(A2)-9).

Example 2.5

Step 6: Press Enter key.

We will be able to see the text string, ‘Time and Tide wait’ in cell B2. Here, the last nine characters from the right side of the string are eliminated.

Example 2.6

Similarly, we can use the LEFT function to display our desired output.

Example #3 – Force the Excel LEFT Function to return a Number

The following table shows the text string in cell A2. We need to use the below steps to obtain the values using LEFT Function in Excel with the VALUE function to fetch only numbers.

Example 3

The steps to use the LEFT Function are as follows:

Step 1:  We should select an empty cell for the output. We have selected cell B2 in this case.

Step 2:  Next, start by entering the formula in cell B2.

Step 3: Enter the VALUE formula along with the LEFT formula.

Step 4: Select the cell having the text. In this example, the selected cell is A2.

Excel Example 3.1

Step 5: Enter the number of digits we want to fetch as a number. In this case, the num_char value will be 3. 

The complete formula is =VALUE(LEFT(A2,3)).

Excel Example 3.2

Step 6: Press Enter key.

Example 3.3

We can see that the function has returned the output (111) in cell B2.

Remember, the VALUE function will only fetch the numeric value in the string according to the number of digits entered in the formula.

LEFT( ) Excel Not Working

The LEFT Function in Excel may not work for the following reasons.

  • The num_chars argument is less than zero: The num_char value should not be less than 0; if so, the LEFT Function will return #VALUE!

Also, the error occurs when the num_char argument is replaced by any other function.

For example, consider the table with the text ‘Papaya’ in cell A1.

Here, assume that we want to display the result in cell B1.

So, insert the LEFT Function in cell B1 with the num_char value less than ‘1’.

So, the complete formula is =LEFT(A1,-1).

Excel Not Working

Press Enter key.

Excel Not Working.1

Clearly, we can see that the output returned by the function has #VALUE! error.

  • Leading spaces in the original text: When the data is copied from other sources, sometimes the leading spaces may be left, which returns the wrong output or errors. For instance, assume that if we copy the text in cell A1 by mistake, some leading spaces are also copied. Then, when we apply the LEFT Function, the function will either return an error or displays incorrect output. 
  • Excel LEFT Function does not work with Dates-: The LEFT Function reads the individual part of a date, such as a day, month, or year. It will retrieve the first few digits of the number that represents that date. For example, we enter the date in cell A1 and then apply the LEFT Function. The dates in excel have numeric values, so the function will not work on the dates. The function will return an error or show an incorrect output.

Important Things To Note

  • The LEFT Function in excel formula should be written correctly.
  • The function returns the value that can be a string or text.
  • The LEFT Function in Excel is used for extracting characters starting from the left side of the text.
  • The num_of_chars argument (one of the two arguments) value is optional, and the default value is set to 1.
  • The function returns #VALUE! Error if the default value is altered.
  • The LEFT Function in Excel extracts digits from numbers as well.

Frequently Asked Questions

What does the LEFT Function do in Excel?

The LEFT Function in Excel extracts the character from the left side of the string based on the argument.

The formula of the LEFT Function is =LEFT(text,[num_char)

For example, consider cell A1 with the text string ‘Calendar’. Let us understand how LEFT Function in Excel works with the following steps.

Left Function in Excel FAQ 1

The steps required to use LEFT function in excel are as follows:

Step 1: Select the cell where we want to display the result. In this case, select cell B1.

Step 2: Type the LEFT Function in cell B1.

The formula is =LEFT(A1).

FAQ 1.1

Step 3: Press Enter key.



We can see that the function has returned the output as ‘C,’ which is the first character of the entered string while taken from the left.

Thus, we can easily use the LEFT function in excel to find values.

When to use the LEFT Function in Excel?

The LEFT Function in Excel is used when we want to fetch the specific characters from the left side of the text string.

Where is the LEFT Function in Excel?

We can manually type the LEFT Function in Excel function in the desired cell or use the following steps:

1. Select the cell which has the number.
2. Select the Formulas tab.
3. Click on the Text option.
4. Select the LEFT option from the drop-down list.

Left Function in Excel.2

5. The Function Arguments window pops up.
6. Enter the value in the Text and num_chars arguments. 
7. Click OK.

Download Template

This article must be helpful to understand LEFT Function in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to LEFT Function in Excel. Here we discuss how to use left formula with step by step example and downloadable excel template. You can learn more form the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.