In this lesson we will learn the usage of LEFT function in Excel. LEFT function is used to extract specified number of characters from left of the string.
- text – The text string that contains the characters you want to extract.
- [num_chars] – This is the number of characters to extract from the left of the text string. It’s an optional argument. If you omit this, it defaults to 1.
- Num_chars cannot be zero or negative.
- If num_chars is greater than the length of the text, LEFT returns the entire string.
- If the number of characters is not given, it returns only the left-most character.
- Spaces are also counted as characters.
- It also works with numbers, as it treats numbers as text.
Let’s understand with some simple example. In the below table, 11 characters from the left of the string in A2 is returned in cell B2 with the formula as follows.
Please note, the space has been also counted as character.
In the below example since there are couple of extra leading spaces in the string in A2, we used TRIM function to remove the leading spaces and then used the below LEFT function in cell B2 to extract 11 characters from the left of the string in cell A2.
In the below example since the number of characters is not given in the LEFT function used in cell B2, it returned only the left-most character from the string in cell A2.
Now we will see how to Extract the text to the Left a Specified Character in a String in Excel.
In the below example there are list of Full Names in column A. We used the below formula containing LEFT & FIND functions to find the First name(which is the text at the left of space “ “).
The FIND function in this formula gives the position of the blank “ “ character. The LEFT function that uses this position to extract the first name.
For example, in the case of Mike Kelly, the FIND function returns 5.
LEFT function then uses FIND(“ ”,A2,1)-1 as the second argument to get the username.
Note that 1 is subtracted from the value returned by the FIND function as we want to exclude the blank space character “ “ from the result of the LEFT function.