RIGHT in Excel: what it does and how to use it

RIGHT takes a set number of characters from the end of a piece of text. LEFT does the same from the start.

Syntax

RIGHT(text, [num_chars])

When to use it

Last four digits of a card, the year off the end of a reference, and the old trick for padding numbers with leading zeros.

What trips people up

Padding works by gluing zeros onto the front and then taking the last few characters from the whole thing. What comes back is text, so it will line up on the left and will not add up. That is usually what you want for a code, and never what you want for a figure.

Watch it built, step by step

Pad a number with leading zeros — Turning 7 into 00007 so codes line up

Questions

How do I take everything after a certain character?

=RIGHT(A2,LEN(A2)-FIND("-",A2)) takes everything after the first dash. On Microsoft 365, TEXTAFTER does it more simply.

Why will the digits from RIGHT not add up?

RIGHT always returns text, even when the characters are digits. Wrap it in VALUE to get a number back.

What do LEFT and MID do?

LEFT takes characters from the start. MID takes them from the middle: =MID(A2,4,2) gives two characters starting at the fourth.

Related functions

TEXTAFTER — TEXTAFTER returns everything that follows a marker you name.
TEXTBEFORE — TEXTBEFORE returns everything that comes before a marker you name, such as a space, a dash or an at sign.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.