Excel Function: LEFT

Purpose

The LEFT function returns a subset of a text value’s characters, taken from its left-hand side.

Characters are returned in the order they appear in the text value.
The number of characters returned is controlled by the “num_chars” argument.
The entire text value is returned if an attempt is made to extract more characters from it, than it contains.
Its output is always a value of type text.

Invocations of the LEFT function take the form:

LEFT(text,[num_chars])

Arguments

Argument	Argument type	Description
text	Mandatory	A text value whose leftmost characters will be returned.
num_chars	Optional. Defaults to 1 if omitted.	A number of characters to be returned from the left of the value passed to the text argument.

Related Functions

Function	Example	Purpose

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	A negative number was supplied to the num_chars argument.
#VALUE!	Bad argument	A value of type currency, geography, or stock was supplied to the text argument.

Examples

Example	Formula	Result	Description
1	`=LEFT("abcdef",0)`		Extract zero (0) of the leftmost characters from a text value. An empty text value is returned.
2	`=LEFT ("abcdef",4)`	`abcd`	Extract fewer characters from a text value than it contains. LEFT operates on “abcdef”. It contains 6 characters. LEFT extracts 4 characters from it. The text value “abcd” is returned.
3	`=LEFT("abcdef",10)`	`abcdef`	Extract more characters from a text value than it contains. LEFT operates on “abcdef”. It contains 6 characters. LEFT attempts to extract 10 characters from it, but can only extract 6. The text value “abcdef” is returned.
4	`=LEFT(123.456,4)`	`123.`	Extract fewer characters from a positive number than it contains. The number 123.456 is converted into the 7-character-long text value “123.456”. LEFT extracts 4 characters from it. The text value “123.” is returned.
5	`=LEFT(-123.456,4)`	`-123`	Extract fewer characters from a negative number than it contains. The number -123.456 is converted into the 8-character-long text value “-123.456”. LEFT extracts 4 characters from it. The text value “-123” is returned.
6	`=LEFT(1E+5,2)`	`10`	Extract the leftmost characters from a number expressed in scientific notation. The number 1E+5 is converted into the 6-character-long text value “100000”. LEFT extracts 2 characters from it. The text value “10” is returned.
7	`=LEFT(DATE(2023,6,7),2)`	`45`	Extract the leftmost characters from a date. The date 7 June 2023 is converted into the 5-character-long text value “45084”. LEFT extracts 2 characters from it. The text value “45” is returned.
8	`=LEFT(TIME(18,0,0),2)`	`0.`	Extract the leftmost characters from a time. The time 18:00:00 is converted into the 4-character-long text value “0.75”. LEFT extracts 2 characters from it. The text value “0.” is returned.
9	`=LEFT("abcdef",-1)`	`#VALUE!`	Extract a negative number of characters from a text value. Extracting a negative number of characters from a text value is nonsensical. The error value #VALUE! is returned.

Examples: Errors

Example	Value supplied to “text” argument	Result	Description
1	`MICROSOFT CORPORATION (XNAS:MSFT)`	`#VALUE!`	Apply the LEFT function to a stock value. The stock value cannot be treated as a text value by the LEFT function. Because of this, the #VALUE! error value is returned.
2	`USD/GBP`	`#VALUE!`	Apply the LEFT function to a currency value. The currency value cannot be treated as a text value by the LEFT function. Because of this, the #VALUE! error value is returned.
3	`London`	`#VALUE!`	Apply the LEFT function to a geography value. The geography value cannot be treated as a text value by the LEFT function. Because of this, the #VALUE! error value is returned.

These errors can be mitigated by converting values of type stock, currency and geography to text before passing them to the LEFT function. I.e.:

=LEFT(VALUETOTEXT(text),[num_chars])