November 25, 2020

LEFT function in Google Sheet

LEFT function is used to extract specified number of characters from left of the string in both Excel & Google Sheet.

=LEFT(text, [num_chars])

Input arguments :

  • 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.

Note :

  • 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.

In the below example, 11 characters from the left of the string in A2 is returned in cell B2. 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 LEFT function.

In the below example since the number of characters is not given, it returned only the left-most character.

Now we will see how to Extract the text to the Left a Specified Character in a String in Google Sheet.

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 “ “).

=LEFT(A2,FIND(“ ”,A2,1)-1)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *