Extract Characters From Data in Excel

Office worker on phone in front of computer
Tim Hawley/Getty Images

The RIGHT, LEFT and MID functions provide a way to extract characters from data in Excel. When text is copied or imported into a cell, unwanted garbage characters are sometimes included with the data you need.

There are also times when you only need specific parts of the text data, such as a person's first name but not their last name. In cases like these, which function you use depends on where the desired data is located relative to the unwanted characters in the cell.

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

Let's look at an example of how to use the RIGHT function to extract characters in Excel. You can follow the same basic steps to utilize the other two functions.

This article applies to Excel 2010, 2013, 2016, and Office 365.

01
of 03

RIGHT Function Syntax and Arguments

Screenshot of Excel showing RIGHT syntax

In Excel, a function's syntax refers to the layout of the function and includes the function's name, parentheses, and arguments. The syntax for the RIGHT function is:

=RIGHT (Text, Num_chars)

The function's arguments tell Excel what data 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_char (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 the Num_char argument is omitted, the default value of 1 character is used by the function. If this is greater than the length of the text, the function returns all text present in Text.

02
of 03

Removing Unwanted Text Characters

Screenshot of Excel showing the Widget example

The example in this image uses the RIGHT function to extract the term Widget from the longer text entry *&^%Widget located in cell B1 in the worksheet. This was achieved by typing the following function into cell C1

=RIGHT (B1,6)

Using the Formula Builder

To make things even simpler, you can select the function and arguments using the Formula Builder, which takes care of the syntax by entering the function's name, commas, and brackets in the correct locations and quantity.

Enter the RIGHT function and its arguments into cell C1 using the Formula Builder like so:

  1. Click on cell C1 to make it the active cell — this is where the results of the function will be displayed.
  2. Click the Formulas tab of the ribbon menu.
  3. Choose Text from the ribbon to open the function drop-down.
  4. Click RIGHT in the list to bring up the Formula Builder.
  5. Click 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 Done to complete the function.
Screenshot of Excel showing how to insert a formula

The extracted text Widget should appear in cell C1. When you click on cell C1, the complete function appears in the formula bar above the worksheet.

Using your mouse to select cells helps prevent errors caused by typing in the wrong cell reference.

03
of 03

Extracting Numbers

Screenshot of Excel showing the VALUE function being utilized

The RIGHT function can be used in the same way to extract a subset of numeric data from a longer number; however, the extracted data is converted to text, so it cannot be used in calculations involving certain functions like SUM and AVERAGE.

=VALUE(RIGHT(B1,4))

One way around this problem is to use the VALUE function to convert the text into a number as shown above. Alternatively, a second option is to use the paste special feature — see Convert Text to Numbers With Excel Paste Special.