Shakil Ahmed
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.
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.
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.
- 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.
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.
- Thus, the LEFT and SEARCH functions will provide the User Name for all the values.
Similar Readings
- How to Use CONCATENATE Function in Excel (4 Examples)
- Use FIND Function in Excel (7 Suitable Examples)
- How to Use CODE Function in Excel (5 Examples)
- Use CLEAN Function in Excel (10 Examples)
- How to Use FIXED Function in Excel (6 Suitable Examples)
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.
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.
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.
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.
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.
