LEFT Formula in Excel (Table of Contents)
LEFT Formula in Excel
LEFT function from Microsoft Excel allows you to extract a substring from a string starting from the leftmost portion of it (that means from the start). It is an inbuilt function in Excel which is specifically defined for string manipulations. Even though you will be giving numeric value as an input, this function will consider it as a string.
Syntax
The syntax for Excel LEFT function is as follows:
The Argument in the LEFT Formula
- text – Required argument which specifies the text/string or the cell containing the string you wanted to extract the substring from.
- num_chars – This is an optional argument that specifies the number of characters to be extracted from the given string from the left-hand side or start of the string.
If omitted, it will be considered as 1, which means returning the first substring from a given string starting from the left-hand side.
How to Use LEFT Formula in Excel?
LEFT Formula in Excel is very simple and easy. Let’s understand how to use the LEFT Formula in Excel with some examples.
Example #1 – Extracting Text from the Left-Hand Side
Suppose we have a string as shown below in cell A1 of the working excel sheet.
Let’s see how we can extract the first three characters from this entire sentence.
In cell B1, start typing the formula for LEFT function as shown below.
Mention A2 as the first argument for the LEFT formula. It specifies the text from which you need to extract the substring.
Add numeric value three as a text specifier which specifies the number of substrings you wanted to extract from the main string.
Your formula is complete. Press the Enter button from the keyboard and see the output of this formula.
This is how the LEFT function can be used. It simply extracted the substring with respective to the number specified from the original string.
Most probably the LEFT formula will be used in combination with some other formulae like SEARCH, VALUE, and FIND.
Example #2 – Extract Substring Before a Certain Character
Suppose you have address details as shown below, which is a combination of State Code and Postal Code from the USA, separated with comma and space.
You want State Codes in a separate column (B). Let’s see how you can do this with the help of LEFT and SEARCH function.
Start typing the formula for LEFT function in cell B2.
Set the text value from which you want to extract the substring. In this case cell A2.
Use SEARCH function as a next argument under the LEFT function after the first argument (A2).
This SEARCH formula will be helping us find out the delimiter with which the two values (State Code and Postal Code) are separated under the Address Details column.
Specify the search criteria (the value which you need to search under given text in cell A2). Here we will provide search criteria as comma (,) Make sure you input it under double quotations so that the system can identify it as a text separator.
Give A2 as the first argument under the SEARCH function.
Close the parentheses for both formulae and press Enter. You will see the following output.
This combination did some work for us, isn’t it? However, it still is not a concrete solution. We still can see the comma after State Code in column B. In order to get rid of that –
Subtract 1 from the SEARCH formula to get rid of a comma. It also helps in removing the trailing spaces from the text.
Now Press the Enter key to see the output one more time.
Apply the formula for all sheets by dragging it down or press CTRL + D by selecting all the cell.
This is the output we actually want. In this way, SEARCH and LEFT in combination can be used to get the substring before a certain character.
Example #3 – How to Get Numerical Output
Excel LEFT formula is specially designed for string manipulation. We already have that discussed. Even if you have some numerical values and you extract some values out of those, those will be recognized as text under excel and further numerical calculations are not at all possible on those values. However, we can force LEFT to give the numerical output in excel. We will see step by step how to do this.
Suppose we have alphanumeric data as below in column A, all we want are the first three numbers of this combination and that too as values in column B.
As we want the result as values, start your formula under cell B1 with =VALUE.
Use the LEFT formula as an argument under VALUE formula. It works because VALUE formula takes text string as an argument and we know that LEFT is a formula associated with string manipulation.
Use the argument under the LEFT function. It is the cell containing the string you wanted to extract a substring from (Cell A2).
Specify the number of substrings you wanted to extract from the given string. Here we wanted the first three letters from the string. So mention 3 after a comma in your formula.
Close the brackets and Press Enter to see the output of this formula.
Drag the formula to all the cells or select all the cells or hit CTRL + D to apply the formula to all the cells.
You can see in the illustration above that, values obtained in column B are right aligned. Which means that these are the values stored as numeric values in that column.
In this way, you can use a combination of VALUE and LEFT function to force the output to be numeric.
This is it from this article, let’s wrap the things up with some points to be remembered.
Things to Remember About LEFT Formula in Excel
- LEFT function can be used when you want to extract the substring from a given string.
- num_chars is an optional argument and takes a default value as 1 if not specified in the formula.
- Currency symbol is not a part of numbers; thus it cannot be included while using LEFT formula.
- #VALUE! error occurs when the num_chars argument has a value of less than 0.
- Dates are being stored as numbers at the backend of Excel, therefore if you apply a LEFT formula to dates, it will give you a numerical string.
Recommended Articles
This has been a guide to LEFT Formula in Excel. Here we discuss How to use LEFT Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
