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.
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.
Table of Contents
1. RIGHT Function Syntax
RIGHT(text,[num_chars])
2. RIGHT Function Arguments
| 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. RIGHT Function 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. RIGHT 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
Formula in cell B5:
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)))
Useful links
RIGHT Function - Microsoft support
How to use Excel RIGHT function - formula examples
'RIGHT' function examples
This post explains how to lookup a value and return multiple values. No array formula required.
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]
Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]
Functions in 'Text' category
The RIGHT function function is one of many functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form