Scenario: You don’t always have control over the format and type of data that you import from an external data source, such as a database, text file or a Web page. Before you can analyse the data, you first need to clean it up. Below we will discuss the REPLACE function; which replaces part of a text string, based on the number of characters you specify, with a different text string.
Take the example of the data in the screen shot below, using a manual method to clean the data will be too laborious to undertake. Below we explain how the unwanted characters in each cell will be replaced with a $ sign using the REPLACE function. To download the workbook to practice, click here.
Applies To: MS Excel 2003, 2007 and 2010
1. With reference to the screen shot above.
2. Select cell C3.
3. Select as per screen shot below.
4. Enter the formula arguments as below.
5. Select OK.
6. The result will be displayed below. The special characters # ! & % ^ will be replaced with the $.
The REPLACE function syntax has the following arguments.
- Old_text : Text in which you want to replace some characters.
- Start_num : The position of the character in old_text that you want to replace with new_text.
- Num_chars : The number of characters in old_text that you want REPLACE to replace with new_text.
- New_text Required. The text that will replace characters in old_text.
