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. For this session, we are using Excel 2019, feel free to use yours (at least 2003).
Practice Workbook
You are welcome to download the practice workbook from the link below.
LEFT Function
1. Basics of LEFT
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.
Summary
Returns a specified number of characters from the start of a text string.
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 |
Versions
Workable from Excel 2003.
2. Uses of LEFT
I. Extract String using LEFT
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. 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. The ID number consists of 4 characters. So our formula will be
=LEFT(B4,4)B4 is the Cell Reference for the first row of the Employee Id-Name column.
The formula provided the desired string we were looking for. Similar formula (changes in the Cell Reference) will provide the Employee ID for the rest of the rows.
II. Extract Text up to a Specific character
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.
Our formula for the first row will be
=LEFT(B4,SEARCH("@",B4)-1)
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.
III. Remove Characters from the End of a String
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 regarding that.
The generic formula will be
=LEFT(text,LEN(text)-num_characters_remove)Â For our example dataset the formula might be similar to the below one
=LEFT(B4,LEN(B4)-7)We want 7 characters to be eradicated from the text stored in B4.Â
The LEN function provided 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.
A similar formula will provide the result for the rest of the texts.
IV. Force to Return Numbers
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.
VALUE converts text that appears in a recognized format into a numeric value. Our formula will be
=VALUE(LEFT(B4,3))
We have found the 3 characters from the string and converted them into numeric.
3. Quick Notes
- If we provide a value less than 0 into the num_chars field
Then it will provide #VALUE! error.
- 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.
Excel stores dates as sequential serial numbers. 1 January 1900 is the 1st date, and its serial number is 1. From there on every date has its subsequent serial number. The functions convert dates to their respective serial number before doing the operations.
If you set the cell containing the date into General you will find the serial number. The formula will extract from that serial number.
- You can provide the text value directly into the function.
You will find the result as expected
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.
Feel free to comment if anything seems difficult to understand. Let us know any of your LEFT function-related scenarios where you have stuck, we are ready to help.
