Tuesday 14 March 2017

Excel - Microsoft Excel Left Function

Description


The Excel Left function returns a specified number of characters from the start of a supplied text string. 

The syntax of the function is : 

LEFT( text, [num_chars] ) 


     Where the function arguments are:


Note that the Left function always returns a text string, even though this may be contain digits and may look like a number. This may be important if you wish to use the result of the function within further functions or formulas. 


Left Function Examples

The spreadsheet below shows three examples of the Excel Left function. 
The format of the functions are shown in the spreadsheet on the left and the results are shown in       the spreadsheet on the right.

Note that the example in cell B3 returns the text value "5". Although the text string contains a number, this is stored as a text string in Excel. Excel Data Types are explained further on the Excel Formatting page


Left Function Error


The Excel Left function occasionally returns the #VALUE! error: 

Common Error

Also, some users encounter the following problem: 

Common Problem 

Some users make the mistake of attempting to use the Left, Mid or Right functions on dates. 

Dates are stored in Excel as numbers, and it is only the cell formatting that makes them appear as dates in your spreadsheet. Therefore, if you attempt use the Left, Mid or Right function on a date, the function will return the end characters of the number that represents that date. 

For example, 01/01/1980 is represented by the number 29221, so applying the Left function to a cell containing the date 01/01/1980 (and requesting that 4 characters be returned) would result in a returned value of "2922". 

Solution No. 1 
Use the Day, Month or Year functions to extract individual parts of a date.



Solution No. 2 
If you are not using the dates in other calculations, which rely on them being stored as numbers, you can convert the cells containing dates to text, using Excel's Text To Columns tool. To do this:

The data in your selected cells should now be stored as text within Excel, and the Left function will work as expected. 








No comments:

Post a Comment