November 25, 2020

RIGHT function in Google Sheet

RIGHT function is used to extract specified number of characters from right of the string in both excel and Google Sheet.

=RIGHT(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 right 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, RIGHT returns the entire string.
  • If the number of characters is not given, it returns only the right-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 right 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 trailing spaces in the string in A2, we used TRIM function to remove the trailing spaces and then used RIGHT function.

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

Now we will see how to Remove Only Leading Spaces in Google Sheet.

In case if you need to remove only the leading spaces from a string in excel or Google Sheet then please use the below formula.

=RIGHT(A1,LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1)

In the below example there are 6 sentences containing leading, trailing and consecutive multiple spaces between words. Using the mentioned formula in column B, all leading spaces from the strings in column A have been removed. But the trailing spaces and the consecutive multiple spaces between words are still there.

This formula uses five different Excel Functions.

  • LEN – This function is used when you want to get the total number of characters in a specified string.
  • TRIM – This function is used to remove the leading, trailing and double spaces.
  • RIGHT – This function is used to extract text from right of the string.
  • FIND – This function is used when you want to locate a text string within another text string and find its position.
  • MID – This function is used to extract a specified number of characters from a string

Here is the breakdown of the formula.

  • MID(TRIM(A1),1,1) – This part of the formula will give you the first character of the string (after removing the leading spaces). For example, in the case of 4th string(A4), there are leading spaces, but this formula would return ‘H’.
  • FIND(MID(TRIM(A1),1,1),A1) – This part of the formula would find the position of the first character in the string. For example, in the 4th string(A4), ‘H’ is in the 9th position (as there are 8 leading spaces before it).
  • LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1 – This part of the formula would give you the total length of the string after removing the leading spaces.
  • RIGHT(A1,LEN(A1)-FIND(MID(TRIM(A1),1,1),A1)+1) – This will give you the result where it extracts all the characters after the leading spaces.

Leave a Reply

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