How to Use the RIGHT Function in Excel

Excel's RIGHT function returns a specified number of characters from the end of a text value. It is useful for extracting suffixes, codes, file extensions, or the portion of a string that follows a delimiter.

RIGHT syntax

=RIGHT(text, [num_chars])

• text: the text or cell to read.
• num_chars: how many characters to return. If omitted, Excel returns one character.

num_chars must be zero or greater. If it exceeds the text length, RIGHT returns the entire value. RIGHT returns text even when the extracted characters look like a number.

Basic examples

If D6 contains an HS code, =RIGHT(D6) returns its last character, while =RIGHT(D6,2) returns the last two.

Extract text after a delimiter

For a value in A2 containing one space, this formula returns everything after that space:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Change the searched character when the delimiter is different:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

SEARCH finds the first occurrence. If the delimiter is missing, the formula returns an error; wrap it in IFERROR only when returning the original value or a clear message is appropriate.

Extract text after the last delimiter

When a colon can appear more than once, first mark the last colon with a character not present in the data, find that marker, and then return the remaining text:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,":","#",LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))

For rows that may not contain a colon:

=IFERROR(RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,":","#",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))),A2)

Remove a fixed prefix

To remove the first six characters, return the total length minus six:

=RIGHT(A2,LEN(A2)-6)

This approach is reliable only when every prefix has the same length. For variable prefixes, locate a delimiter instead.

Convert extracted digits to a number

=VALUE(RIGHT(A2,5)) converts the last five characters to a number:

Do not convert identifiers such as postal codes if leading zeros must be preserved. The VALUE function guide explains text-to-number conversion in more detail.

Why RIGHT gives unexpected results with dates

Excel stores real dates as serial numbers. RIGHT therefore reads the serial value, not the displayed year, month, or day.

Use =DAY(A1), =MONTH(A1), or =YEAR(A1) for a real date.

If an imported date is plain text, RIGHT may extract characters from its displayed string, but the result depends on a consistent layout.

Troubleshooting RIGHT

• #VALUE! usually means num_chars is negative.
• Unexpected spaces may require TRIM before extraction.
• Wrong text after a delimiter often means SEARCH found an earlier occurrence.
• Numbers returned as text may need VALUE, but identifiers should usually stay as text.

For extracting other positions, compare the DAY, MONTH, and YEAR functions or use LEFT and MID for text at the beginning or middle.