Left function in excel is a type of text function in excel which is used to give the number of characters from the start from the string which is from left to right, for example if we use this function as =LEFT ( “ANAND”,2) this will give us AN as the result, from the example we can see that this function takes two arguments.
LEFT Function in Excel
The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function. LEFT function in Excel can either be used a worksheet function (WS) or a VBA function (VBA) in Microsoft Excel. In terms of worksheet function, LEFT function in excel can be entered as a formula in a cell of Excel worksheet. In terms of VBA function, LEFT function in excel can be used in macro code which you can enter through the Microsoft Visual Basic Editor.
LEFT Formula in Excel
The LEFT formula in excel accepts the following parameters and arguments:
text– Required. This is referred to as the string that you wish to extract from.
number_of_characters – [Optional]. This is referred to as the number of characters that you want to extract which is starting from the leftmost character. Note down that if this parameter is omitted; only 1 character will be returned.
The return value will be either a string or a text value.
LEFT Function in Excel Usage Notes
- The LEFT function in excel should be used for extracting characters starting from the left side of the text.
- Number_of_characters is optional and defaults to 1.
- LEFT in excel will extract digits from numbers as well.
- Number formatting (i.e. the currency symbol $), is not part of a number. Therefore these are not counted or extracted by the EXCEL LEFT Function.
How to Open the LEFT Function in Excel?
Below are some of the examples of LEFT Function in Excel.
- You can simply enter the desired LEFT formula in excel in the required cell to attain a return value on the argument.
- You can manually open the LEFT formula in excel dialogue box in the spreadsheet and enter the Text and Number of Characters.
- From the above spreadsheet, you can see the LEFT Formula in excel option under the Text Tab of Formulas Tab in the menu bar.
- To open the LEFT Formula in excel dialogue box, click on the Text Tab under the Formulas Tab, and click LEFT. See the sample below.
- The above dialogue box will open where you can put the arguments to attain a Formula Result.
Some Formulas of LEFT Function in Excel
Extracting a substring before a certain character:
You can use the below given generic formula for getting a substring that precedes any other character –
The above LEFT formula in Excel is used when you want to extract the part of the text string which is preceding a specific character. For example, if you want to pull the first names from a column of full names or you want to extract the country codes from the list of phone numbers.
Removing the last N characters from a string:
You can use the following generic formula for removing the last N characters from a string.
The above LEFT formula in excel is used when you want to remove a certain number of characters from the end of the string and pull the rest of the string into another cell. The working of the LEFT formula in excel is based on the logic: LEN function is used to get 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.
How to Use LEFT Function in Excel with Examples
Let’s look below at some of the examples of LEFT function in Excel. These examples will help you in exploring the use of LEFT function in Excel.
In the above spreadsheet, the LEFT formula in excel written is =LEFT (A1, 5).
The result based on the syntax of the LEFT function is TOMAT.
Let’s look at some more examples –
LEFT in Excel Example #1
When the LEFT formula in excel is written is =LEFT (A2, 8)
Consider the excel spreadsheet below.
LEFT in Excel Example #2
When the LEFT formula in excel is written is =LEFT (“Excel”, 2)
Consider the excel spreadsheet below.
LEFT in Excel Example #3
When the LEFT formula is written is = LEFT (“EXCEL”)
Consider the excel spreadsheet below
LEFT in Excel Example #4
When the LEFT formula is written is =LEFT (“Excel”, 25)
Look at the spreadsheet below to understand the above example.
LEFT in Excel Example #5
Consider the data below which is put in an excel spreadsheet.
- Original Text
These are the return values for Excel LEFT Function mentioned.
- When LEFT formula in excel entered is =LEFT(A7,4), the result is 8552.
- When LEFT formula in excel entered is =LEFT(A8,5), the result is 91-98.
- When LEFT formula in excel entered is =LEFT(A9,5), the result is Origi.
- LEFT Function VBA
You can also use the LEFT function VBA Code in Microsoft Excel.
Look at the below examples to understand the LEFT function as a VBA function.
LEFT function VBA Example #1
Extracting a substring of length 4 from the start of the string “Kelly Michael”.
The variable res is now equal to the text string “Kell”.
In the above example, the LEFT function VBA returns the result “Kell”.
LEFT Function VBA Example #2
Extracting a substring of length 8 from the start of the string “Kelly John Michael”.
The variable res will now be equal to the text string “Kelly Joh”.
In the above example, VBA LEFT function returns the result “Kelly Joh”.
Things to Remember About the LEFT Function in Excel
- LEFT in Excel is a function by which you can extract a substring from a string, starting from the left most character.
- The return value can be string or text value.
- The return value depends on the arguments which you input in the formula syntax of OR function.
- #VALUE! error – Occurs if the given (number_of_characters) argument is less than 0.
- Dates are put away in Excel as numbers, and it is just the cell formatting that makes them show up as dates in our spreadsheet. Therefore, if we attempt to use the Excel LEFT function on a date, it will return the starting characters of the number that represents that date.
This has been a guide to LEFT Function in Excel. Here we discuss the LEFT Formula in excel and how to use LEFT Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –