Excel LEFT Function – Extract Text from Left Side

This blog covers one of the most used formulas in Excel viz. the excel LEFT function. The LEFT function, as the name itself suggests, is used to extract a certain number of characters from the beginning or start of a text in excel.

This tutorial covers purpose of LEFT function, its syntax, arguments, and how to use LEFT function in excel with the help of examples.

Let’s begin. Here we go 😎

When to Use LEFT Excel Function

The LEFT excel formula is useful to separate or extract the specific number of characters from the left side of a text.

This function returns a text string as its output.

Syntax and Arguments

=LEFT(text, [num_chars])

Below bullet points explain the two arguments of the LEFT formula in excel.

  • text – In this argument, specify the text from which you want to extract the sub-string. It can be either manually entered or as a reference to another cell.
  • [num_chars] – In this optional argument, specify the number of characters to extract from the left.

If you keep the [num_chars] argument as blank, then it will take ‘1’ as by default. In this case, the LEFT formula will extract first character from a text.

Examples of LEFT Function in Excel

In this section, we would learn about how does the LEFT function work in Excel with the help of some examples.

Ex. 1 # Extract First Character from a Cell or Text in Excel

The below image shows a list of material codes wherein the first letter denotes the batch number of that material.

Material Code List - LEFT Function Example

To get the first character (batch number) from every cell, use the following formula in cell B2:

=LEFT(A2)

As a result, excel would return the first character (viz. batch code) in column B, as shown in the image below:

Using LEFT Function in Excel - Example

Explanation – In the above example, since we have not used the [num_chars] argument, excel by default extracts and returns the first character.

Ex. 2 – Extract Specific Number of Characters from the Left of the Text

Let’s say that you want to extract a specific number of characters from the left of the cell.

In that scenario, use the [num_chars] argument as explained via below example.

Extract Specific Characters From Text

Suppose you want to extract the first three characters from each of the above-listed material codes. To achieve this, simply enter the value 3 as [num_chars] argument.

=LEFT(A2,3)
Extract First Three Characters in Excel Text

Ex. 3 – Extract Text Before a Particular Text or Character

The above learned two examples were basic examples to understand how the LEFT formula functions in excel.

Infographic - LEFT Formula Function in Excel

However, many times you may not know what should be the exact [num_chars] value, because it may differ in each of the scenarios.

To best understand such a scenario, let us take the below example:

Material Codes with Different Batch Length

In the above example, the alphabet portion represents batch number and the number portion represents material.

Since for each of the material codes, the length of the batch number varies, it is impossible to use a fixed [num_chars] value.

In that case, to extract the text from the left before the first space, you need to use the LEFT function in conjuction with FIND or SEARCH excel formula.

=LEFT(A2,FIND(" ",A2)-1)

OR

=LEFT(A2,SEARCH(" ",A2)-1)
Using LEFT function with FIND function

Explanation – In the above example, the FIND and the SEARCH function searches for the space character, and returns its position.

This method is widely used to extract the first name from the full name in excel. Check our blog post on how to separate the first name from the full name in excel.

The LEFT function belongs to the text function group. Therefore, it returns the value from the left of the string, and stores it in the text format, irrespective of the original number. Check the below section.

How to make LEFT Function Return A Number?

As mentioned above, the LEFT function, being a text function returns a text string and not a number. This means that a number output of the LEFT function can not be used to perform any calculation, because it is a text string.

To let the LEFT formula return a number, instead of a text string, simply convert it into value by embedding it into the VALUE function. See below syntax:

=VALUE(LEFT())
Using LEFT function with VALUE function

Explanation – The VALUE function converts a number string into a proper number. In the above example, the LEFT(A2,3) function extracts the first three numbers from text in cell A2 and then the VALUE function converts that text into a number.

Thank You 🙂

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.