What does it do?

It returns the last character or characters in a text string, based on the number of characters you specify.

Formula breakdown:

=RIGHT(text, [num_chars])

What it means:

=RIGHT(look in this cell, extract X characters)


There are times when you will need to extract the last few characters of text within a cell, e.g. From a serial number, part number, name, address etc.

The RIGHT formula in Excel can help you parse and extract the needed text easily.

In our example below, we have an address which has a zip code at the end (the zip code is fixed at 5 characters long) and we want to extract all of the zip codes in our address list.

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

DOWNLOAD EXCEL WORKBOOK

 
STEP 1: We need to enter the Right function next to the cell that we want to extract the data from:

=RIGHT

 

STEP 2: The Right arguments:

text

Which text do we want to extract the last X characters from?

Reference the cell that contains the text or value:

=RIGHT(C9

Right 01

[num_chars]

How many characters from the right do we want to extract from cell C9?

Enter a positive number only:

=RIGHT(C9, 5)

Right 02

 

STEP 3: Do the same for the rest of the cells by dragging the RIGHT formula all the way down using the left mouse button.

Notice all of the last 5 characters in each text are now extracted:

Right 03

 

Right

HELPFUL RESOURCE:

data cleansing course

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Cleaning Data with Excel’s PROPER Formula  What does it do?Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all other letters to lowercase letters.Formula breakdown:=PROPER(text)What it means:=PROPER(this text cell)There are...
Cleaning Data with Excel’s SUBSTITUTE Formul... What does it do?Substitutes new_text for old_text in a text string.Formula breakdown:=SUBSTITUTE(text, old_text, new_text, )What it means:=SUBSTITUTE(This cell, By this text character, To this text character, )When you needed to replace a specific text...
Getting the remainder with Excel’s MOD Formu... What does it do?Gives you the remainder after dividing one number with anotherFormula breakdown:=MOD(number1, number2)What it means:=MOD(a number, divided by this number)In Excel it is very easy to divide two numbers.But how about if you need to ge...
Add Comma After the Surname with Excel’s SUB... What does it do?It replaces specific text in a text string i.e. Substitutes new text for old text in a text string.Formula breakdown:=SUBSTITUTE(text, old_text, new_text, )What it means:=SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, )...