Left function in Excel What is that? How to use the Left function in Excel how? Let's find out with Quantrimang.com!
Microsoft Excel is famous as a spreadsheet software capable of handling large amounts of data. You can import data in bulk, then use smart Excel functions to calculate the results yourself. In this article, we will learn about the LEFT function – one of Excel's common text functions.
The LEFT function belongs to the string processing function group, used to cut the character string to the left of the text string. The LEFT function is often used to quickly search for information, instead of having to search for information or character strings manually. In particular, the LEFT function can be combined with other lookup functions in Excel to process complex information tables such as combining the LEFT function with the Vlookup function. The article below will guide you how to use the LEFT function in Excel.
LEFT function formula in Excel
The LEFT function has the syntax: =LEFT(text;[num_chars]). In there:
- Text is the required text string, which is a text string or cell reference to a text string that contains the characters you want to extract.
- Num_chars is an optional argument, this is the number of characters you want the LEFT function to search for starting from the first position on the left of the text.
- Num_chars must be greater than or equal to zero, if num_chars
- Num_chars is greater than the length of the text, the LEFT function will return the entire text.
- If num_chars is omitted, the default is num_chars = 1.
Note when using the LEFT function in Excel
- The LEFT function in Excel should be used to extract characters starting from the left side of the text.
- Number_of_characters is optional and defaults to 1.
- LEFT in Excel will also extract digits (0-9) from numbers.
- The number format (that is, the $ currency symbol) is not part of the number. Therefore, they are not calculated or extracted by the LEFT function.
How to open LEFT function in Excel?
1. You just need to enter the desired LEFT formula in Excel in the required cell to achieve the return value on the argument.
2. You can manually open the LEFT formula in the Excel dialog box in the spreadsheet and enter the text and number of characters.
3. From the spreadsheet above, you can see the LEFT function formula option in Excel under the tab Text in section Formulas on the menu bar.
4. To open the LEFT formula in the Excel dialog box, click the tab Text in Formulas and choose LEFT. See sample below.
5. The above dialog box will open where you can set the arguments to achieve the formula result.
Some LEFT function formulas in Excel
Extracts a substring before a certain character
You can use the general formula below to get a substring preceded by any other character:
The above LEFT formula in Excel is used when you want to extract a part of a text string, preceded by a specific character. For example, if you want to get names from a column of full names or you want to extract country codes from a list of phone numbers.
Remove the last N characters from a string:
You can use the following general formula to remove the last N characters from a string.
The above LEFT formula in Excel is used when you want to remove certain characters from the end of a string and pull the rest of the string into another cell. The operation of the LEFT formula in Excel is based on logic: The LEN function is used to get the total number of characters in a string. This LEFT function formula in Excel excludes the number of unwanted characters from the total length and returns the remaining characters.
Instructions for using the LEFT function in Excel
Example 1: Use the LEFT function to find characters
In the table below, use the LEFT function to find the first 3 characters in cell B2. Enter the formula =LEFT(B2,3) and press Enter.
As a result, we get 3 characters from left to right of the character string in cell B2.
Or in the input formula, you can replace the position of the cell containing the string of characters with a character enclosed in quotation marks as shown.
Example 2: LEFT function combines SEARCH function
When combining these two functions together, we will use it to search for the string of characters preceding a certain character, for example, get the last name in the full name column, get the country code minus the phone number. In the full name column, all are separated by spaces, so we use the formula = =LEFT(B2,SEARCH(” “,B2)-1) and then press Enter.
Then -1 to not extract space characters when searching for characters.
As a result, you get the last name string in the cell. Scroll down to the boxes below for other results.
For the phone number range where you want to get the country area code before the dot, enter the formula =LEFT(B5,SEARCH(“.”,B5)-1) and press Enter.
The result will only get the country area code in the phone number range.
Example 3: Combine the LEFT function with the LEN function
The LEN function is often used in combination with functions to find character strings. With the LEFT function, when combined with the LEN function, it is used to remove certain characters from the end of the string. Combined formula =LEFT(text,LEN(text) – character you want to move).
The LEN function takes the total number of characters in a string, then subtracts the number of characters you want to remove from the total length of the string. The LEFT function will return the number of remaining characters.
For example, remove 5 characters from the string in cell B2, enter the formula =LEFT(B2, LEN(B2)-5) and press Enter.
As a result, we have the remaining character sequence after removing the last 5 characters in the character sequence, including spaces.
Example 4: Combine LEFT function and VALUE function
When these two functions are combined together, they will return numeric characters, instead of text strings like when using the LEFT function. For example, export the first 2 characters of the string in cell B5, enter the formula =VALUE(LEFT(B2,2)).
As a result, we get the number we need to find as shown.
LEFT function in Excel does not work – causes and solutions
If Excel's LEFT function is not working properly in your worksheet, it is most likely due to one of the following reasons.
1. The Num_chars argument is less than 0
If your Excel LEFT formula returns an error #VALUE!the first thing for you to check is the value in the argument num_chars. If it's a negative number, just remove the minus sign and the error will go away (of course, it's very unlikely that someone intentionally put a negative number there, but it's very normal for humans to make mistakes).
Usually, the VALUE error occurs when the argument num_chars represented by another function. In this case, copy the function to another cell or select it in the formula bar and press F9 to see what value that function is equivalent to. If the value is less than 0, then check the function for errors.
To better illustrate this problem, let's use the LEFT formula used to extract country phone codes: LEFT(A2, SEARCH(“-“, A2)-1). As you may remember, functions SEARCH in argument num_chars calculates the position of the first hyphen in the original string, from which we subtract 1 to remove the hyphen from the final result. If you accidentally replace -1, for example, with -11, the formula will return an error #VALUE because of the argument num_chars equivalent to a negative number: The formula on the left doesn't work because of the argument num_chars smaller 0.

2. Line spacing in the original text
In case your Excel LEFT formula fails for no apparent reason, check the original values for leading spaces. If you have copied your data from the web or exported from another external source, many such spaces may be before text entries without you realizing it, and you will never know they are there for you. until something goes wrong. The following image illustrates an issue with Excel's LEFT function not working properly due to a space at the beginning of the original string.

To remove spaces at the beginning of your worksheet, use Excel's TRIM function or the Text Toolkit add-in.
3. Excel LEFT does not work with dates
If you try to use Excel's LEFT function to get individual parts of a date (such as day, month, or year), in most cases you'll only get the first few digits of the number. represents that day. The problem is that in Microsoft Excel, all dates are stored as integers representing the number of days since January 1, 1900, which are stored as the number 1. What you see in a cell is just a representation The visual appearance of the date and its display can be easily changed by applying a different date format.
For example, if you have the date 11-Jan-2017 in cell A1 and you try to extract the date using the formula LEFT(A1,2), the result will be 42, which is the first 2 digits of the number 42746 . represents January 11, 2017 in the internal Excel system.
To extract a specific portion of a day, use one of the following functions: DAY, MONTH, or YEAR.
In case your date is entered as a text string, the LEFT function will work without a hitch, as shown in the right part of the screenshot: Excel LEFT does not work with dates but does work with a text string representing the date.

4. Loss of currency symbol
If the original text has currency or accounting formatting applied, this symbol is not part of the characters in the cell. This icon appears only when it is accompanied by a specific number format. The LEFT function is a text function. So it will convert all values to text format.
A useful solution here is to combine LEFT with the VALUE function to convert text to a number. You can then select the desired number format.
5. The LEFT function returns fewer characters than expected
If you know the number of characters that make up the desired output but the LEFT function formula returns only a few characters, you may need more space for the original text.
Instead of deleting them manually, you can use the TRIM function to remove them.
=LEFT(TRIM(A2),7)
Some things to keep in mind when using the LEFT function in Excel
- Error
#VALUE
appears if argument [num_chars] less than 0. - Dates are classified in Excel as numbers, and only cell formatting makes them appear as dates in the spreadsheet. Therefore, if you want to use the LEFT function for dates, it will return the starting characters of the number representing the date.
For example, 01/01/1980 represents the number 29221, so applying the LEFT function to a cell containing the date 01/01/1980 (and requiring it to return 3 characters) will give you the result 292. If If you want to use LEFT on dates, you can combine it with the DAY, DATE, MONTH or YEAR functions. If not, you can convert cells containing dates to text using Excel's text-to-column tool.
The above is How to use LEFT function get the string from the left and examples when combining the LEFT function with other functions. If an error occurs, the user needs to check again num_chars
is greater than 0 or not.
Wishing you success!