Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use LEFT Function in Excel (4 Suitable Examples)

Excel provides several text functions to help you perform your desired tasks easily and swiftly. Today we are going to show you how to use a text function called: LEFT function in Excel. For this session, we are using Excel 365, feel free to use yours (at least 2003).

You can use the LEFT function to get value from the counting from the left. Here, you can see that we have used this function to extract value from text or number values. In the formula, the 1st argument is used to identify the value and the second one represents the number of characters you want to extract.

Excel LEFT Function


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Introduction to Excel LEFT Function

The LEFT function is categorized under the TEXT functions in Excel. This function returns a specified number of characters from the start of the provided text string.

  • Function Objective:

This function is used to find the several characters of a text from left according to the number you provide.

  • Syntax:

=LEFT (text, [num_chars])

  • Arguments:
Argument Required/Optional Description
text Required The text string that contains the characters to extract.
num_chars Optional The number of characters to extract. By default 1.

  • Return Parameter:

Returns a specified number of characters from the start of a text string.

  • Version:

This function is available in Excel 2007 to all the newer versions after that.


4 Suitable Examples of Using LEFT Function in Excel

1. Basic Use of LEFT Function: Extract String from Left Side

From the description of the LEFT function, you might have understood that this function will help you extract a string from the beginning (left) of a text. Thus, all you need to do is to set the text and the number of characters you want.

For example, we have a dataset of several employees with their respective Ids and Names.

Here from the Employee Id-Name column, we will extract the Id portion.

Extract String Using LEFT Function in Excel

To do that, follow the steps given below.

Steps:

  • Firstly, select Cell D5 and insert the following formula.
=LEFT(B5,4)
  • Then, press Enter and drag-down the Fill Handle tool to autofill this formula for the rest of the cells.

In the formula, we inserted Cell B4 as text and 4 as num_chars.
  • Thus, the LEFT function will provide the Employee ID for all the values.


2. Insert Excel LEFT & SEARCH Functions to Extract Text up to Specific Character

Additionally, the LEFT function will help you fetch a text searching for any specific character.

To show you examples, we have brought a dataset of a few email addresses. We will find the User Name from the email.

Since we need to look for a specific character, our formula will be a nested one. We will use the SEARCH function along with LEFT.

The SEARCH function returns the location of one text string inside another.

Insert Excel LEFT & SEARCH Functions to Extract Text up to Specific Character

Follow the steps given below to extract text up to a specific character.

Steps:

  • In the beginning, insert the following formula in Cell C5.
=LEFT(B5,SEARCH("@",B5)-1)
  • After that, press Enter.
  • Then, drag-down the Fill Handle tool to use this formula for the rest of the Cells.

Here, we have searched for @, the SEARCH function would return the position of @. We wanted up to @, so we subtracted 1 from the position number. This provided the user name.
  • Thus, the LEFT and SEARCH functions will provide the User Name for all the values.


Similar Readings


3. Remove Characters from End of a String Using LEFT & LEN Functions

Using LEFT we can extract the desired text by removing the characters from the end of the string.

Our example dataset has the names of a few athletes. We are set to find their first name from the full name.

To remove the characters first we need to find the length of the string, the LEN function will help us with that.

Remove Characters from End of String Using LEFT & LEN Functions in Excel

Steps:

  • Firstly, select Cell C5 and insert the following formula.
=LEFT(B5,LEN(B5)-7)
  • After that, press Enter and drag-down the Fill Handle tool to autofill this formula for the rest of the cells.

🔎 How Does the Formula Work?

  • Firstly, the LEN function is used to determine the total number of characters of the given string.
  • Then, the LEFT formula removes the unwanted part of the string.
  • Lastly, the LEFT function returns the rest of the characters in Excel.

  • Finally, you will get the Athlete’s First Name using the LEFT and LEN functions.

Excel LEFT Function


4. Force to Return Numbers Applying Excel LEFT & VALUE Functions

We can fetch numbers from text. The string we store, usually are in text format. We can fetch the digit value from the string and convert them to a number.

Our example dataset contains several IDs for employees.

The first 3 characters are digits. We will fetch these 3 characters as a number. The VALUE function will be useful for our operation.

Force to Return Numbers Applying Excel LEFT & VALUE Functions

Here are the steps to return the first 3 characters from the IDs.

Steps:

  • Firstly, select Cell C5 and type the following formula.
=VALUE(LEFT(B5,3))
  • Next, press Enter and drag down the Fill Handle tool.

🔎 How Does the Formula Work?

  • In the beginning, The LEFT function returns the first 3 characters from the ID.
  • Then, the VALUE function converts text that appears in a recognized format into a numeric value.

  • Thus, using the formula, we have found the 3 characters from the string and converted them into numeric.


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Things to Remember

  • If we provide a value less than 0 into the num_chars field, then it will provide #VALUE! Error.
  • Secondly, for dates, you may not find the exact value you are wanting. From a date, if you want to find the day portion, you may write LEFT with 2 in the num_chars field. But will find another value, not the day value of the provided date.
  • Usually, Excel stores dates as sequential serial numbers. 1 January 1900 is the 1st date, and its serial number is 1. From there every date has its subsequent serial number. Thus, the functions convert dates to their respective serial number before doing the operations.
  • Therefore, if you set the cell containing the date into General you will find the serial number. The formula will extract from that serial number.
  • Finally, you can also provide the text value directly into the function.

Read More: How to Use Excel EXACT Function (6 Suitable Examples)


Conclusion

That’s all for today. We have tried showing how you can use the LEFT function. You can use the function to extract text from the beginning of a string as well as several advanced operations can be performed. Hope you will find this helpful.

However, feel free to comment if anything seems difficult to understand. Additionally, let us know any of your LEFT function-related scenarios where you have stuck, we are ready to help.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo