The LEFT function in excel is used to extract a specific number of characters from the left side of a text string. The function extracts from the first character on the left-hand side of a text string, up to the number of characters specified by the user through the num_chars argument. In today’s post, we would take you through all you need to know about the LEFT Function and how you can apply it to financial modeling. Let’s start things off with its syntax and argument.
Syntax & Argument
The syntax of the LEFT function is as follows:
=LEFT(text, [num_chars])
The LEFT function has only 2 arguments as can be seen above, one is required and the other is optional. Let’s see which is which below.
text: This argument represents the cell containing text or hardcoded text string from which you want to extract characters.
num_chars: This is the optional argument, and it allows the user specify the number of characters to the left of the text string to extract. If the num_chars is not provided by the user, the LEFT function extracts only one character from the left side of the text string.
Our next stop is a summary of the usage notes of the LEFT function.
Usage Notes
- The text argument must be a text or a reference to a cell containing a text string. If the text argument is not a text string, the LEFT Function would return a #VALUE error.
- The num_chars argument must be a positive integer or refer to a cell containing a positive integer. If the num_chars provided is zero or a negative number, the LEFT function will return an empty string (” “).
- If the num_chars is omitted, the RIGHT Function will return the first character to the left of the text.
- If the num_chars argument is greater than the length of the text argument, the LEFT function will return the entire text argument.
- The RIGHT Function can be combined with other functions such as CONCATENATE, FIND, LEN for more complex text manipulation.
- If the text argument contains non-printable characters such as line breaks, the LEFT Function may not return the expected result.
Left Function Examples
Hardcoded Text
For our first example, we will extract the first four characters to the left side of our hardcoded text which is the word “Golden”. Let’s look at how it looks below:
From Cell Reference
Following on from the above, we would add a new text string on the row below, however we want to extract the first 3 characters leftmost to the text string, however we need to select the text character from a cell and not hardcode it like we did in the previous example.
LEFT Function & CONCATENATE
The next stop is combining the LEFT Function with the concatenate function. In our example for this section, we have order details of some customers for a restaurant that sells only 3 products, and we want a summary column for these orders.
In our screenshot above, our invoice details combine the invoice number and the food type but we are only interested in extracting the invoice number for our summary column. Our summary column should contain the customer’s name, food type, food quantity and invoice number in a readable text. The formula to use is as follows:
Now, just copy and paste to the other cells and khalas.
Extract Text to left of Character
We can extract text to the left of a specific character by combining the LEFT function and FIND function. We have a simple example, let’s show you how to do it and the thinking behind it.
Our text above is Rice_krispies and our target is to extract all text to the left of the underscore delimeter (“_”). To do that, we use the formula below:
Explaining the formula
The FIND Function locates the position of the character we specify which in our case is the underscore delimiter (“_”). It outputs the number position, and we include a minus 1 to capture all the characters just before the delimiter because we don’t want to capture the delimiter along with the extract.
In the LEFT Function, the result of the FIND Function is taken as the num_chars argument, and our function is complete. In our example, the FIND function returns 5 as the result, then we subtract 1 from it to get a final count of characters before it. The final count of characters is 4, and the left function outputs 4 characters to the leftmost side of the text.
