Hi Excellers, welcome to another Formula Friday. Today I want to share with you a really quick way to tidy up your data and get rid of those pieces of data or indeed swap out pieces of data you do not want in your data set.
Here is an example of this method I demonstrated to an Excel user this week, using the SUBSTITUTE function. But first, let’s take a quick look at the syntax of this handy formula.
Where
- Text This is requested. The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text This is also required. The text you want to replace.
- New_text This is also required. The text you want to replace old_text with.
- Instance_num This is optional. It specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text is replaced.
First let’s take a look at a sample data set, it contains some data regarding serial numbers of our stock of Super Start Batteries. You can see that the data is slightly inconsistent, which we can easily fix using SUBSTITUTE.
We have a couple of options with SUBSTITUTE, depending on what we want to do with the data. We could replace the SER with SN for example or we could remove it altogether from the data. We also have the option to specify which occurrence of the data we want to amend, for example the first, second third etc, or not specify so we amend all occurrences. Let’s look at all the options.
Scenario 1. Replace “SER” with “SN” prefix.
We have left the Instance_Num part of the formula blank, so by default all of the instances are changed, in this data set we only have one instance of it, so it is irrelevant
Scenario 2. Remove “SER” altogether by replacing it with blanks.
Scenario 3. Substitute the second instance of “0” and replace it with “2” as we know that those batteries are recorded incorrectly with no batteries having the name Super Start Battery 2010.
Substitute is useful when you want to replace text when you base the decision on the cell content i.e SER, 0 rather than position of the text in your cell.
Have you used SUBSTITUTE?
Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
