When data is imported or copied into an Excel spreadsheet unwanted characters or words can sometimes be included with the new data.
Excel has several functions that can be used to truncate or remove such unwanted characters. Which function you use depends upon where the unwanted characters are located.
- If you have unwanted characters on both sides of your good data, use the MID function to remove them.
- If these unwanted characters appear on the left side of your good data, use the RIGHT function to remove them.
- If the unwanted characters are on the right side of your good data, use the LEFT function to remove them.
The RIGHT Function
The syntax for the RIGHT function is:
= RIGHT ( Text , Num_chars )
Text - the piece of data you want to change.
This can be a cell reference indicating where the data is stored.
Num_chars - specifies the number of characters to be retained from the Number specified above.
Example Using Excel's RIGHT Function to Truncate Text
- Enter the following data into cell C1: #687
- Click on cell D1 in the spreadsheet - this is where the function will be located.
- Click on the Formulas tab of the ribbon menu.
- Choose Text from the ribbon to open the function drop down list.
- Click on RIGHT in the list to bring up the function's dialog box.
- In the dialog box, click on the Text line.
- Click on cell C1 in the spreadsheet.
- Click on the Num_chars line.
- Enter the number 3 on this line since we only want to keep the three rightmost characters of data.
- Click OK.
- The number 687 should appear in cell D1.
- When you click on cell D1 the complete function = RIGHT ( D1,3 ) appears in the formula bar above the worksheet.