When data is imported or copied into an Excel spreadsheet unwanted characters or words can sometimes be included with the new data.
Excel's REPLACE function can be used to replace these unwanted characters with good data or with nothing at all.
The REPLACE function makes it easy to quickly correct long columns of imported data. As with most functions, create the function for the first entry and then use the fill handle or copy and paste to copy the function to all other cells.
The syntax for the REPLACE function is:
=REPLACE(Old_text, Start_num, Num_chars, New_text)
Old_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 start position (from the left) of the characters in old_text that you want to replace.
Num_chars - specifies the number of characters to be replaced from the Start_num specified above.
New_text - specifies the new data to be added. This argument can be left blank if you just want to remove unwanted characters.
Example: Using Excel's REPLACE Function
For help with this example, see the image above.
- Enter the following data into cell D1: ^& # 24,398. The ^, & , and # characters are located above the numbers 6, 7, and 3 on the top row of the keyboard.
- Click on cell E1 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 REPLACE in the list to bring up the function's dialog box.
- In the dialog box, click on the Old_text line.
- Click on cell D1 in the spreadsheet since the data we want to alter is in cell D1.
- Click on the Start_num line.
- Enter the number 1 on this line to indicate we want to start our replacement from the first character on the left.
- Click on the Num_chars line.
- Enter the number 3 on this line to replace the first three characters.
- Click on the New_text line.
- Type a dollar sign ( $ ) so that we replace the three characters with the dollar sign.
- Click OK.
- The amount $24,398 should appear in cell E1.
- When you click on cell E1 the complete function = REPLACE ( D1 , 1 , 3 , " $ " ) appears in the formula bar above the worksheet.