The Excel LEFT Function
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:
The original text string.
An optional argument that specifies the number of characters to be returned from the start of
the supplied text.
If omitted, the [num_chars] argument takes on the default value of 1.
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
Column B of the following spreadsheet shows three examples of the Excel Left function.
||=LEFT( A1 )
||=LEFT( A2, 4 )
||=LEFT( A3, FIND( " ", A3 ) - 1 )
Note that in the above examples:
- The function in cell B1 omits the [num_chars] argument and so in this
case, [num_chars] is set to the default value 1.
- In cell B3, the Excel Find Function is
used to find the position of the first space in the text string "Original Text". This result is then
provided to the Left function, to split the original text string at the first space.
For further information and examples of the Excel Left function, see the
Microsoft Office website.
Left Function Error
If you get an error from the Excel Left function, this is likely to be the #VALUE! error:
Occurs if the supplied [num_chars] argument is < 0.
Also, some users encounter the following problem:
Common Problem - Using the Left Function With Dates
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 start, mid or 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,
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:
- Use the mouse to select the cells you want to convert to text (this
must not span more than one column);
- From the Data tab on the Excel ribbon, select the
Text to Columns ... option;
- Ensure the Delimited option is selected and click Next;
- Ensure all the Delimiters are unchecked and click Next;
- You should now be offered a selection of Column Data Formats. Select Text and click
the Finish button.
The data in your selected cells should now be stored as text within Excel, and the Left function
will work as expected.