The LEFT function in Excel helps extract a certain part of a given text string based on the defined number of characters mentioned in its formula. It is one of the most important and must-know functions in Excel.
The formula for the LEFT function is simple despite being an advanced-level function. And there are so many uses of it for anyone working with the Excel program. In this guide, we will discuss everything there is to know about this extremely useful text function.
Besides demonstrating some excellent uses, we will also discuss solutions to a few problems Excel users may face when they use this function. We are also sharing the workbook with you which we have used for this guide. You can download and follow it for an improved understanding.
- Excel LEFT Function: Syntax Breakdown
- How Does the LEFT Function Work?
- Some Important Uses of the LEFT Function in Excel
- Use 3: Separating Department Names from ID
- Why is the LEFT Function Not Working?
- Last Words
What is the LEFT Function?
In Excel, the LEFT function is one of the most used text functions. Depending on the value mentioned in the formula for the number of characters you want, the LEFT function picks up that specified amount of characters from the beginning of a text string.
The function can be accessed in more than one way. You can go to the Formulas ribbon and click the Text button in the Function Library. From the drop-down menu, you can find LEFT and click on it.
You can also go to the Formulas ribbon, click Insert Function, and then write LEFT in the box under Search for a function. Then click Go and select the LEFT function from the list below.
Following either option will open up a Function Arguments window where you may enter your arguments.
But the most classic way is to write the formula for the LEFT function in a cell. You begin with a “=” and then write the function name, LEFT, followed by open parentheses. Then you enter your arguments, close the parentheses, and hit Enter to let the function do its work.
You can use any of the techniques we have shown above.
Now let’s break down the formula for this amazing LEFT function.
Excel LEFT Function: Syntax Breakdown
In the explanation of the LEFT function, we will see what each argument in the formula means. The formula is =LEFT(text,[num_chars]).
- LEFT : The function.
- text : The text string from where you want to extract characters from. You can write the string of text in this argument or you can do a Cell Reference to direct it to the cell with the text string.
- [num_chars] : Number of characters. This is an optional argument. It defines how many characters from the beginning of the text string you want to extract. If you do not input anything for this argument, the formula will extract only the first character from the string of text.
By using this formula, we will show you how you can extract wanted information from a dataset.
How Does the LEFT Function Work?
The mechanism of the LEFT function is as simple as its formula. There are only two steps involved.
1. It goes to the leftmost character of the given text string.
2. Then it counts characters from the left based on the specified value mentioned in the “num_chars” argument and returns that number of characters to the cell where you have written the formula.
For instance, look at the image below.
Cell A1 has the text “Closure” in it. We entered the formula in the cell next to it and specified that the text is in cell A1. In the “num_chars” argument, the mentioned number of characters we wanted the LEFT function to extract is 3 characters. Now see the result.
The formula has returned the first three characters of the text string in the example, “Clo”. Pretty amazing, isn’t it?
Some Important Uses of the LEFT Function in Excel
As an Excel user, you can use the LEFT function in countless ways. This is one of those functions that works wonders with other functions as well. It helps reduce your workload.
In this section, we decided to show you some simple yet necessary uses of the LEFT function in Excel. These uses will give you a primary idea of what you could do with this excellent function.
Use 1: Extracting Initials
Pulling out initials from a name is a pretty familiar task in Excel. Users who don’t know a quick way to do it tend to write the initials manually, one by one. However, the LEFT function helps extract it in seconds even if you have thousands of names in your dataset.
Take an empty column and write the formula. Do a Cell Reference to the cell with the name for the text argument. Then in the “num_chars” argument, you may put “1” or simply not put anything at all. The default value of it is “1” when nothing is mentioned.
Finally, hit Enter.
You will see the initial of the name you referred to in your formula. Fill the column with the formula by using the Fill Handle.
Now you have extracted the initials from all the names on your list!
Use 2: Extracting Area Code
This is also a usual situation when you need to get the area codes from a list of numbers. The LEFT function will let you do it so fast.
Write the formula for the LEFT function. Put “3” for the number of characters you want to extract. It is because the first three characters from the beginning of a phone number are the area code.
After pressing Enter, you will get the area code for your selected cell. Fill the column with your formula to get area codes from each phone number in your source column.
Much easier and faster than singling out area codes manually, isn’t it?
Use 3: Separating Department Names from ID
If you need to extract each employee’s department name from their IDs, it is super easy to do; thanks to the LEFT function.
First, find out how many characters in employee ID numbers occupy the department names. In this example, the first two characters in each ID indicated the respective employee’s department name.
After finding out the number of characters, write the formula in an empty cell.
Afterward, you will get the department names extracted in the cell you have written your formula. Use the Fill Handle to fill up the column and find out all the employees’ department names, separated and well-formatted.
We can bet there is no faster way to do this task!
Use 4: Extracting First Names
When you are given the task to extract first names from a list of full names, use this technique with the LEFT function next time to save both your time and energy. However, since not all the first names have the same number of characters, we are going to apply some tweaks to make the value dynamic.
In this use of the LEFT function, we are including another function called the SEARCH function to help us do the job faster. In brief, the SEARCH function will find the number of characters from the first space character found in the given full name. It will count characters to the beginning of the text string from the first space character it has found.
Follow the image below. Write down your formula. In the place of the “num_chars” argument, put the SEARCH formula as shown below. The “-1” in the end is to exclude the space character itself from the calculation.
After you enter the formula and then fill the column with it, you will see that all the first names from your list of names have been extracted.
This is just one tremendous use of the LEFT function. There are many other combinations of formulas with the LEFT function that will help you do your job more smoothly. Read the next example for another excellent application of its combination.
Use 5: Listing User ID from Email Address
The LEFT function helps you find out the user ID from email addresses with ease. We will combine the FIND function with the LEFT function here. You may use the SEARCH function too instead of the FIND function. They do the same work and follow the same arguments.
In the previous use, the space character after the first name was found out and with the help of the LEFT function, the first name was returned. Here, the goal is to find the “@” character and extract all the characters leading to it.
See the image below. Write the formula for finding the user ID as shown in the picture. Like the previous use with First Names, the “-1” in the end before closing the parentheses is added to exclude the “@” character itself from being counted in the number of characters.
When you are done entering the formula and press the Enter button, you will see that all the user IDs have been extracted.
Pretty neat, right?
Why is the LEFT Function Not Working?
There are moments when the LEFT function may not work. In case you face a similar situation, we are going to discuss the four topmost reasons why the LEFT function may stop working and give you a hard time. Follow the solutions accordingly.
Reason 1: The Calculation Results Option is Set to Manual
If the Calculation Option in the Calculation group under the Formulas tab is set to Manual, you will face a certain problem while using the LEFT function. See the picture below.
After writing the formula in the first cell, you would use the Fill Handle to fill the column and get results for each respective data. However, you may see that all the cells below are showing the same result as the first cell.
To solve this problem, go to the Formulas ribbon. Then in the Calculation group, click the Calculation Options and then select Automatic from the drop-down menu.
It will solve this problem for you instantaneously!
Reason 2: Leading Spaces or NonPrintable Characters
When there are leading spaces or NonPrintable characters in the front, the LEFT function will not work properly. This problem originates from having to copy-paste data from a non-Excel document.
You cannot detect it easily if your copied data has leading spaces or NonPrintable characters like a Tab character at the beginning of it. See the image below for an example. The spaces in the front cannot be detected by looking at the cell but if you notice it in the formula bar, the spaces are visible there.
You can remove those leading spaces and NonPrintable characters with the help of the TRIM function. Just combine your LEFT formula with a TRIM formula and it will work wonderfully!
Reason 3: Wrong Cell Formatting
The LEFT function is a TEXT function. If the source text string is in a number format, the LEFT function will not work. But how would you know if this is the reason behind your problem?
First, select your cell. Then in the Home ribbon, notice the formatting in the Number group. You can also right-click on your cell and then click Format Cells to see the current formatting. See the image below for a better understanding.
To change the formatting, simply select General or Text from the Category. The LEFT function will work afterward.
Reason 4: The #VALUE! Error
The #VALUE! error is also a common occurrence when you work with the LEFT function. Let’s say you input a LEFT formula but have no idea why this error is all you are getting.
The #VALUE! error mainly shows up if the “num_chars” argument in your formula has a negative number in it. See the image below.
Do not use a negative value and the #VALUE! error will go away instantly.
The LEFT function has some incredible uses in Excel as a part of the Text function group. Its potential gets multiplied when the user knows how to combine it with other functions. We have shown a few uses that may help you get a kick-start in the realm of the LEFT function.
As you get to know about an Excel function more and more, you will realize when to use it and how to use it. And to develop this sense, practicing the known uses of that function is the most impactful way. Therefore, we strongly recommend you understand how this LEFT function works, what the arguments are, and what those arguments do.
When you learn it all, you will find some exceptional uses of the LEFT function whenever you work in Excel. Make the LEFT function a part of your daily Excel routine, and be a master of it in no time!