The Excel RIGHT Function

01
of 01

Remove Unwanted Characters from Imported Data

Removing unwanted text with the RIGHT function in Excel
Removing unwanted text with the RIGHT function. © Ted French

RIGHT Function Overview

When text is copied or imported into Excel, unwanted garbage characters are sometimes included with the good data.

Or, there are times when only part of the text data in the cell is needed - such as a person's first name but not the last name.

For instances like these, Excel has a number of functions that can be used to remove the unwanted data from the rest. Which function you use depends upon where the good data is located relative to the unwanted characters in the cell.

  • If the good data is on the left side of the data, use the LEFT function to extract it.
  • If the good data has unwanted characters on both sides of it, use the MID function to extract it.
  • If the good data is on the right side of the data, use the RIGHT function to extract it.

The RIGHT Function Syntax and Arguments

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the RIGHT function is:

= RIGHT ( Text , Num_chars )

The function's arguments tell Excel what data it is to be used in the function and the length of the string to be extracted.

Text - (required) the entry containing the desired data
- this argument can be a cell reference to the location of the data in the worksheet or it can be the actual text enclosed in quotation marks

Num_chars - (optional) specifies the number of characters on the right of the string argument to be retained - all other characters are removed
- this argument must be greater than or equal to zero
- if this argument is omitted, the default value of 1 character is removed by the function
- if Num_chars is greater than the length of text, the function returns all of text

Example: Removing Unwanted Characters with the RIGHT Function

The example in the image above uses the RIGHT function to

  • extract the term Widget from the longer text entry *&^%Widget located in cell B1 in the worksheet
  • extract 789876 from the longer number 123456789876 located in cell B2 in the worksheet

Below are listed the steps detailing how the first result was obtained

Entering the RIGHT Function

Options for entering the function and its arguments into cell B1 include:

  1. Typing the complete function: = RIGHT ( B1.6 ) into cell C1.
  2. Selecting the function and arguments using the function's dialog box

Using the dialog box to enter the function often simplifies the task as the dialog box takes care of the function's syntax - entering the function's name, the commas separators, and brackets in the correct locations and quantity.

Pointing at Cell References

No matter which option you choose for entering the function into a worksheet cell, it is probably best to use pointing to enter any and all cell references used as arguments.

  • Pointing involves using the mouse pointer to click on a cell reference in order to enter it into a function.
  • Doing so, helps to eliminate errors caused by typing in the wrong cell reference.

Using the RIGHT Function Dialog Box

Listed below are the steps used to enter the RIGHT function and its arguments into cell C1 using the function's dialog box.

  1. Click on cell C1 to make it the active cell - this is where the results of the function will be displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Text from the ribbon to open the function drop down list
  4. Click on RIGHT in the list to bring up the function's dialog box
  5. In the dialog box, click on the Text line
  6. Click on cell B1 in the worksheet
  7. Click on the Num_chars line
  8. Type in the number six ( 6 ) on this line since we only want to keep the six rightmost characters
  9. Click OK to complete the function and return to the worksheet

The extracted text Widget should appear in cell C1

When you click on cell C1 the complete function = RIGHT ( B1, 6 ) appears in the formula bar above the worksheet

Extracting Numbers with the RIGHT Function

As shown in the second row of the example above, the RIGHT function can be used to extract a subset of numeric data from a longer number using the steps listed above. The only problem is that the extracted data is converted to text and cannot be used in calculations involving certain functions - such as the SUM and AVERAGE functions.

One way around this problem is to use the VALUE function to convert the text into a number. For example:

=VALUE ( RIGHT ( B2, 6 ))

A second option is to use paste special to convert the text to numbers.

Format
mla apa chicago
Your Citation
French, Ted. "The Excel RIGHT Function." ThoughtCo, Apr. 24, 2017, thoughtco.com/excel-right-function-3123545. French, Ted. (2017, April 24). The Excel RIGHT Function. Retrieved from https://www.thoughtco.com/excel-right-function-3123545 French, Ted. "The Excel RIGHT Function." ThoughtCo. https://www.thoughtco.com/excel-right-function-3123545 (accessed January 23, 2018).