What is the Excel LEFT Function?
The excel LEFT function takes a number of characters in the text from the left.
LEFT Syntax
LEFT(text, [num_chars])
text, required, the text from which to extract the characters.
num_chars, optional, specifies the number of characters to extract.
- If you omit the num_char argument, it is assumed to be 1.
- If the num_chars argument is larger than the length of the text, you got the text
- If you put a negative number on the num_char argument, you got a #VALUE! error
How to Use LEFT Function in Excel
The following is an example of the LEFT function usage and the results
LEFT Function #1
=LEFT(A2,0)
You got nothing if you put zero number on the num_char argument.
LEFT Function #2
=LEFT(A3,3)
Nothing special. You fill number 3 in the num_char argument; then you get the first three letters of the abcde text.
LEFT Function #3
=LEFT(A4,1000)
Regardless of how you fill the num_char argument, if it is larger than the text length, you got the same text.
LEFT Function #4
=LEFT(A5,-1)
You get the #VALUE! Error if you fill a negative number in the num_char argument.
LEFT Function #5
=LEFT(A6,2)
The LEFT function can extract the alphanumeric value because alphanumeric is a text.
LEFT Function #6
=LEFT(A7,2)
The LEFT function can extract numeric values too. Both alphanumeric or numeric values the LEFT function return the same result.
LEFT Function #7
=LEFT(A8,2)
Whatever the format cell applied to a number, the LEFT function ignores it and only extracts the number.
LEFT Function #8
=LEFT(A9,2)
Why LEFT function returns the same result as the previous 3 LEFT functions for date value 1/1/2019? Excel stores date values in an integer number. Date 1/1/2019 is the same as number 43466.
Read the following article for a more detailed explanation of how Excel stores date and time values
LEFT Function #9
=LEFT(A10,2)
Why the LEFT function returns the different results for cell A10? Cell A9 contains a date, but cell A10 contains a text. LEFT function counts slash line for text values.
The otherwise, date (day, month and year) and slash line is the format of the cell, not the data. The actual data is an integer number.
LEFT Function #10
=LEFT(A11,3)
The LEFT function can extract decimal numbers too. LEFT function count decimal separator as one character.
LEFT Function #11
=LEFT(A12,3)
Why does 1:00:00 PM give the same results as the previous LEFT function? Excel stores date values as integer numbers, and for times values Excel stores as a decimal number.
Read “How does excel stores date and time” for a detailed explanation.
