RIGHT
Cutting Up & Piecing Together Text Strings
Basic Description
The Excel Right function returns a specified number of characters from the end of a supplied text string.
The syntax of the function is :
Where the function arguments are:
| text | - | The original text string |
| [num_chars] | - |
An optional argument that specifies the number of characters to be returned from the end of the supplied text If omitted, the [num_chars] argument takes on the default value of 1 |
Note that the Right function always returns a text string, even though this may be contain digits and may look like a number. This may be important if you wish to use the result of the function within further functions or formulas.
Right Function Examples
The spreadsheet below shows three examples of the Excel Right function.
Note that the example in cell B3 returns the text value "5". Although the text string contains a number, this is stored as a text string in Excel. Excel Data Types are explained further on the Excel Formatting page of this site.
Further information and examples of the Excel Right function are provided on the Microsoft Office website.
Right Function Errors
If you get an error from the Excel Right function, this is likely to be the #VALUE! error:
| #VALUE! | - | Occurs if the supplied [num_chars] argument is < 0 |
Also, some users encounter the following problem:
Some users make the mistake of attempting to use the Left, Mid or Right functions on dates.
Dates are stored in Excel as numbers, and it is only the cell formatting that makes them appear as dates in your spreadsheet. Therefore, if you attempt use the Left, Mid or Right function on a date, the function will return the start, mid or end characters of the number that represents that date.
For example, 01/01/1980 is represented by the number 29221, so applying the Right function to a cell containing the date 01/01/1980 (and requesting that 4 characters be returned) would result in a returned value of "9221".
Use the Day, Month or Year functions to extract individual parts of a date.
If you are not using the dates in other calculations, which rely on them being stored as numbers, you can convert the cells containing dates to text, using Excel's Text To Columns tool. To do this:
- Use the mouse to select the cells you want to convert to text (this must not span more than one column)
- From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
- Ensure the Delimited option is selected and click next
- Ensure all the Delimiters are unchecked and click next
- You should now be offered a selection of Column Data Formats. Select Text and click the Finish button.
The data in your selected cells should now be stored as text within Excel, and the Right function will work as expected.