How to use the RIGHT function

What is the RIGHT function?

The RIGHT function extracts a specific number of characters always starting from the right.

1. Introduction

What is the difference between the RIGHT function and the LEFT function?

The major difference between the LEFT and RIGHT functions in Excel is the direction they extract characters from a value:

LEFT extracts from the left side of the text string. It starts from the first character and returns the specified number of characters from the left.

RIGHT extracts from the right side of the text string. It starts from the last character and returns the specified number of characters counting backwards from the right.

LEFT is useful for extracting leading characters like codes or prefixes. RIGHT is useful for extracting ending characters like file extensions or suffixes. LEFT is a more common operation as starting characters often distinguish values, RIGHT has more advanced uses.

What other Excel functions manipulate text?

LEFT | RIGHT | MID | TEXTAFTER | TEXTBEFORE | TEXTSPLIT | TEXTJOIN | CONCAT

What is the difference between the RIGHT function and the RIGHTB function?

The RIGHT and RIGHTB functions handle character counting differently based on your default language setting in Excel. RIGHTB is designed for double-byte languages like Chinese, Japanese, Korean. It counts each double-byte character as 2 when a DBCS language is set as the default.

RIGHT always counts each character as 1, single-byte or double-byte, regardless of language setting. So RIGHTB will count each double byte character as 2 contrary to the RIGHT for the same text in a DBCS language.

Japanese, Chinese (Simplified), Chinese (Traditional), and Korean are some examples of languages supporting DBCS.

2. Syntax

RIGHT(text,[num_chars])

text	Text string or a cell reference to a text string.
[num_chars]	The number of characters to extract. Optional. If this argument is not entered only the first character is extracted.

3. Example

The RIGHT function extracts a specific number of characters from right.

Formula in cell C7:

The image above shows the value "Brown horse" in cell B7, the RIGHT function in cell C7 extracts the five last characters from cell B7. Cell C7 shows "horse" which are the five last characters in "Brown horse".

4. Function not working

The RIGHT function returns #VALUE! if the num_chars argument is below 0 (zero).

The #NAME error appears if the function name is misspelled.

Other errors may have their origin in the source data. Check the source data for errors.

5. Mark each character in a value with a number from right

This formula splits a string into individual characters and then create an array of numbers that count down from the total number of characters to 1, effectively numbering each character from right to left. The image above shows a value in cell B3, the formula output is in cell B5. This is an Excel 365 formula that spills the output array to adjacent cells below and to the right as far as needed.

Formula in cell B5:

The formula in cell B5 lets you easily count the characters of a given value from right to left making it much easier to use the RIGHT function and specify the number of characters to extract.

Here's a breakdown of how it works:

1. LEN(B3): This function returns the length of the string in cell B3, which is the total number of characters.
2. SEQUENCE(,LEN(B3)): Creates a sequence from 1 to n which represents the number of characters in cell B3.
3. MID(B3, SEQUENCE(,LEN(B3)), 1): This function extracts individual characters from the string in cell B3. The 1 at the end specifies that only one character should be extracted at each position. This creates an array of individual characters.
4. SEQUENCE(,LEN(B3),LEN(B3),-1): This function generates a sequence of numbers from n to 1, decrementing by 1 each time. This creates an array of numbers that count down from the total number of characters to 1.
5. VSTACK(...): This function stacks the results of steps 3 and 4 vertically, creating an 2D array.
6. LET(x, LEN(B3), ...): This function assigns the result of LEN(B3) to a variable x, which is then used multiple times in the rest of the formula.

Explaining formula

VSTACK(MID(B3,SEQUENCE(,LEN(B3)),1),SEQUENCE(,LEN(B3),LEN(B3),-1))

Step 1 - Count characters in cell

The LEN function returns the number of characters in a cell value.

Function syntax: LEN(text)

LEN(B3)

becomes

LEN("What does the fox say?")

and returns 22.

Step 2 - Create a sequence from 1 to 22 horizontally

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(,LEN(B3))

becomes

SEQUENCE(,22)

and returns

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}

Step 3 - Split characters in cell B3 to an array

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B3,SEQUENCE(,LEN(B3)),1)

becomes

MID("What does the fox say?",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22},1)

and returns

{"W","h","a","t"," ","d","o","e","s"," ","t","h","e"," ","f","o","x"," ","s","a","y","?"}

Step 4 - Stack arrays vertically

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(MID(B3,SEQUENCE(,LEN(B3)),1),SEQUENCE(,LEN(B3),LEN(B3),-1))

becomes

VSTACK(MID(B3,SEQUENCE(,LEN(B3)),1),SEQUENCE(,22,22,-1))

becomes

VSTACK({"W","h","a","t"," ","d","o","e","s"," ","t","h","e"," ","f","o","x"," ","s","a","y","?"},{22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1})

and returns

{"W","h","a","t"," ","d","o","e","s"," ","t","h","e"," ","f","o","x"," ","s","a","y","?";22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1}

Step 5 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

VSTACK(MID(B3,SEQUENCE(,LEN(B3)),1),SEQUENCE(,LEN(B3),LEN(B3),-1))

x - LEN(B3)

LET(x,LEN(B3),VSTACK(MID(B3,SEQUENCE(,x),1),SEQUENCE(,x,x,-1)))

Back to top

Useful links

RIGHT Function - Microsoft support
How to use Excel RIGHT function - formula examples

'RIGHT' function examples

Functions in 'Text' category

The RIGHT function function is one of 29 functions in the 'Text' category.

Excel function categories

Excel categories

Leave a Comment