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 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 MID Function
The syntax for the MID function is:
= MID ( Text , Start_num , Num_chars )
Text - the piece of data you want to change.
This can be a cell reference indicating where the data is stored.
Start_num - specifies the starting character from the left of the data to be kept.
Num_chars - specifies the number of characters to the right of the Start_num to be retained.
Example Using Excel's MID Function:
Note: For help with this example, see the image to the right.
- Enter the following data into cell C1: #687 miles
- 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 MID 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 Start_num line.
- Enter the number 2 on this line since we don't want to keep the first character on the left.
- Click on the Num_chars line.
- Enter the number 3 on this line since we only want to keep three characters of data (the number 687).
- Click OK.
- The number 687 should appear in cell D1.
- When you click on cell D1 the complete function = MID ( D1,3 ) appears in the formula bar above the worksheet.