In this lesson we will learn the usage of RIGHT function in Excel. The RIGHT function is used to extract specified number of characters from right 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 right 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, 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.
Now let’s understand with a simple example. In the below table, 11 characters from the right of the string in A2 is returned in cell B2 using the formula as follows.
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 cell B2 as follows to extract 7 characters from the right of the string in cell A2.
In the below example since the number of characters is not given in the following formula used in cell B2, it returned only the right-most character from the string in cell A2.
Now we will see how to Remove Only Leading Spaces in Excel.
In case if you need to remove only the leading spaces from a string in excel then please use the below formula.
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.