LEFT Function in Excel

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

 

The LEFT function in Excel is a type of text function in Excel that gives the number of characters from the start of the string, from left to right. So, for example, if we use this function as =LEFT ( “ANAND”,2) this will give us AN as a result. From the example, we can see that this function takes two arguments.

LEFT Function in Excel

The Microsoft Excel LEFT function is a function that allows you to extract a substring from a string and starts from the leftmost character. It is a built-in function in excel, categorized as a "String/Text Function." The LEFT function in Excel can either be used as a worksheet function (WS) or a VBA function (VBA) in Microsoft Excel. The LEFT function in Excel can be entered as a formula in an Excel worksheet cell in terms of worksheet function. In terms of the VBA function, we can use the LEFT function in Excel in macro code, which we can enter through the Microsoft Visual Basic Editor.

Table of contents

LEFT Formula in Excel

LEFT Formula

The LEFT formula in Excel accepts the following parameters and arguments:

text- Required. It is referred to as the string that you wish to extract from.

number_of_characters – . It is referred to as the number of characters you want to extract, starting from the leftmost character. Note that if this parameter is omitted, it will return only 1 character.

Return Value:

The return value will be either a string or a text value.

LEFT Function in Excel Usage Notes

  • We should use the LEFT function in Excel for extracting characters starting from the left side of the text.
  • Number_of_characters is optional and defaults to 1.
  • The LEFT function in Excel will extract digits from numbers as well.
  • Number formatting (the currency symbol $) is not part of a number. Therefore these are not counted or extracted by the EXCEL LEFT function.

How to Open the LEFT Function in Excel?

Below are some of the examples of LEFT functions in Excel.

 

  1. We can insert the desired LEFT formula in Excel in the required cell to attain a return value on the argument.
  2. We can manually open the LEFT formula in the Excel dialog box in the spreadsheet and enter the text and number of characters.

LEFT Function step 1

  1. We can see the LEFT formula in the Excel option under the "Text" tab of the "Formulas" tab in the menu bar from the above spreadsheet.
  2. To open the LEFT formula in the Excel dialog box, click on the "Text" tab under the "Formulas" tab and click "LEFT." See the sample below.

LEFT Function step 2

  1. The above dialogue box will open where you can put the arguments to attain a “Formula result.”

Some Formulas of LEFT Function in Excel

Extracting a substring before a certain character:

We can use the below given generic formula for getting a substring that precedes any other character:

LEFT Formula 1

The above LEFT formula in Excel is used when we want to extract the part of the text string which is preceding a specific character. For example, if we are going to pull the first names from a column of full names or we want to extract the country codes from the list of phone numbers.

Removing the last N characters from a string:

You can use the following generic formula for removing the last N characters from a string.

LEFT Formula 2

The above LEFT formula in Excel is used when we want to remove a certain number of characters from the end of the string and pull the rest of the string into another cell. The working of the LEFT formula in Excel is based on the logic: the LEN function is used to get the total number of characters in a string. The LEFT formula in Excel subtracts the number of unwanted characters from the total length, and the LEFT function in Excel returns the remaining characters.

How to Use LEFT Function in Excel with Examples

1. Worksheet Function

Let us look below at some examples of the LEFT function in Excel. These examples will help you explore the use of the LEFT function in Excel.

LEFt Example 1

In the above spreadsheet, the LEFT formula in excel written is =LEFT (A1, 5).

The result based on the syntax of the LEFT function is TOMAT.

Let’s look at some more examples –

LEFT in Excel Example #1

When the LEFT formula in Excel is written is =LEFT (A2, 8)

Result: amazon.c

Consider the Excel spreadsheet below.

LEFt Example 1

LEFT in Excel Example #2

When the LEFT formula in Excel is written, as =LEFT (“Excel”, 2)

Result: “Ex”

Consider the Excel spreadsheet below.

LEFt Example 2

LEFT in Excel Example #3

When the LEFT formula is written as = LEFT (“EXCEL”)

Result: “E”

Consider the Excel spreadsheet below

LEFt Example 3

LEFT in Excel Example #4

When the LEFT formula is written, as =LEFT (“Excel”, 25)

Result: “Excel”

Look at the spreadsheet below to understand the above example.

LEFt Example 4

LEFT in Excel Example #5

Consider the data below, which is put in an Excel spreadsheet.

  • 85522-10002
  • 91-98125-55237
  • Original Text

These are the return values for the Excel LEFT function mentioned.

LEFt Example 5

  • When the LEFT formula in Excel is entered as =LEFT(A7,4), the result is 8552.
  • When the LEFT formula in Excel is entered as =LEFT(A8,5), the result is 91-98.
  • When the LEFT formula in Excel is entered as =LEFT(A9,5), the result is Origi.
  1. LEFT Function VBA

You can also use the LEFT function VBA Code in Microsoft Excel.

Look at the below examples to understand the LEFT function as a VBA function.

LEFT function VBA Example #1

Extracting a substring of length 4 from the start of the string Kelly Michael.

LEFT function as a VBA function example 1

The variable res is now equal to the text string Kell.

In the above example, the LEFT function VBA returns the result, Kell.

LEFT Function VBA Example #2

Extracting a substring of length 8 from the start of the string Kelly John Michael.

LEFT function as a VBA function example 2

The variable res will now equal the text string “Kelly Joh.”

In the above example, VBA LEFT function returns the result “Kelly Joh.”

As we explore this topic, it’s interesting to note how a deeper understanding can enhance practical skills. For those looking to expand their expertise, consider checking out this Working With Advanced Excel Functions Certification Course that delves into this further.

Things to Remember

  • The LEFT in Excel is a function by which we can extract a substring from a string, starting from the leftmost character.
  • The return value can be a string or text value.
  • The return value depends on the arguments we input in the formula syntax of the OR function.
  • #VALUE! Error – This occurs if the given (number_of_characters) argument is less than 0.
  • Dates are put away in Excel as numbers, and it is just the cell formatting that makes them show up as dates in our spreadsheet. Therefore, if we attempt to use the Excel LEFT function on a date, it will return the number's starting characters that represent that date.

Recommended Articles

This article is a guide to LEFT Function in Excel. We discuss the LEFT formula in Excel, use the LEFT Excel function and Excel examples, and downloadable Excel templates. You may also look at these useful functions in Excel: -

  • VBA LEFT
  • SUM Function
  • Trim in Excel | Examples
  • INDEX Excel Function
  • SUMIFS with Dates