Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In addition to standard kinds of replacement techniques available with Find and Replace, you may need to have to deal with special characters, like the asterisks that we see in Column D. And there is also another function, one called Substitute, that in many ways solves problems that Find and Replace can't handle. Let's deal, first of all, with the issue in Column D. Who knows how those asterisks got in there? We simply want to get rid of them, or possibly substitute some other character for them. Let's click Column D. And using Find & Select, the rightmost button on the Home tab in the Ribbon, let's go to Replace, and we would like to replace that asterisk-- that's all we want to replace.
We don't want to match the entire cell; we simply want to replace the asterisk with, how about nothing? So this is empty. Replace All. 742 replacements, but look at Column D. It's all gone. So how do we deal with that? What we've got to do for now is click OK, close, and undo. I'll press Ctrl+Z here. The asterisk is a special wildcard character. Perhaps you've encountered it in the other uses of Excel, perhaps in the filter. Let's go back to Find & Select.
We need to somehow refer to that asterisk in a special way. We'll do a replace. What we need to do here, and it's an unlikely character to use, is the tilde character. This is that squiggly little character that appears above the letter N in certain Spanish words. On the keyboard, usually this is found below the Escape key, to the left of the number 1 key, and above the Tab key on the upper left-hand corner. So if we embed the asterisk within tildes, two of them there, we want to replace all the asterisks in here with nothing, so we'll leave the Replace with panel empty.
Let's do a Replace All. There we go, and you see that's happened on the background. Turns out there are only four of them, but we did get rid of them. So that's an unusual construction. You might need that at different times when you're trying to clean up data. Now sometimes a coding-structure change is needed. We saw one example in a previous movie about how to make the third character become an X, using a function called Replace. Now we've got a different situation, maybe working off the same data. We want to replace the 2 that's in here, but only the first 2, and it might occur in the third position, as it does in a lot of these, or it might occur elsewhere.
It might not occur there at all. Let's replace the first occurrence of 2 with a certain character. Again, we're not just saying that to make it up. Based on the concept, we really need to get rid of that first 2. The function is called Substitute. We're looking at cell B2, comma. The text we're looking for is 2, comma. And what we want to replace it with is, for example, the letter Q. Now if it's a letter, we need to embed that within double quotes.
And then you'll see, after this in the prompt, instance number, comma, 1. If we leave off that argument, we will replace all the 2s with Q. So, you see what happened. The third entry there, that's the first 2 that we encountered, becomes a Q, but not the fourth entry, even though it's a 2, but the function, remember, says, just the first instance, because we've put in a 1 there. Let's copy this down the column and check out a few more. A lot of these that begin with 2, well, you see immediately what's happened: the Q is out there instead.
Others like this one, the 2 doesn't appear until way over here, and it gets replaced with the Q. When there are multiple 2s, only the first one gets replaced with Q. So, pretty arcane stuff in a certain sense, but the tool that's definitely needed when you're in that situation and you've got to clean up data. You're trying to make wholesale changes to codes. Substitute function works fine for this. And previously in this movie, we saw how to use the Find & Select feature for unusual characters, like the asterisk.
We used a tilde to embed the asterisk within it to replace those kinds of entries.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 99029 Viewers
80 Video lessons · 141744 Viewers
59 Video lessons · 60111 Viewers
52 Video lessons · 73254 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.