Replace characters by position with the REPLACE function

- [Voiceover] Excel has two functions that are very similar. One is called replace, another is called substitute. And there's also a command called replace that can get a little confusing because the replace command is very similar to the substitute function, it's not similar to the replace function. Start with the idea the substitute function, similar to the replace command, it's based on content. So, got a list of code numbers here and we want to remove all dashes. We can use the function called substitute, but it's gonna be simpler and faster to simply use the replace command.

From the home tab, far button, the magnifying glass, find and select, replace, pull it down here, we want to replace what? The dash with what? Nothing, replace all of them and replace tells us we made eight replacements. So the dashes are gone from those entries right there, OK. We don't need to use the substitute function. Similarly here, replace all dashes with underscore, we could do that in the same kind of way, this time we will do this with a different symbol.

Once again, find and replace, replace. This time we're replacing all dashes, replace with what? And by the way you can replace this with more than one entry. So I'm gonna put in underscore, we can certainly do that, but maybe I'll put in two underscores, that's okay, you can replace one with zero, one or many. Replace all, there it is, nine replacements. I wanna do that over again. Of course I could undo and just do it with one, but you see what's happening here. If it's a later time and I wanna undo this I'll come back and find two underscores and then replace them with one underscore.

Same set of data, replace all, there we are, nine replacements. So, easy to use, we don't need that function at least for these kinds of examples. But we do need them here because the substitute function allows us to remove or to substitute only the first occurrence of an entry, or the second, or the third, something like that, we can make our choices that way. And the word remove might seem a little bit out of place here, but we can use substitute by in effect removing something.

But we only wanna get rid of the first dash here. So here's the substitute function. Here's the text we're looking at comma item that we're actually referring to within double quotes is a dash, that's the so called old text comma the new text that we want here is nothing double quote, double quote, and the instance number is our last choice here, if we only wanna remove the first dash we put in a one and enter, and you see what's happening. The first dash is gone, but not the second. Do this for the others, like that.

Over here we see the same kind of function, here it's already entered, here we're removing the dash just like we did before and replacing it with XXX, but we're only doing it for the second instance and so, seeing here, we're gonna see 384-RHXXX87, 384-RH. We didn't remove the first dash, so even though we say dash right here, and this is what we're replacing it with, two here refers to the second occurrence.

Now the replace function has to do with position. So you notice the description here, replace the fifth character, we don't care what it is, we're gonna replace it with X. And so this certainly has nothing to do with that command where we use find and select and replace. We will use the replace function here, and here too based on the description above we can replace a character or characters with none, one or more characters, zero, one or more characters based on position. So, here's the text we're looking at, comma here's the starting number, the fifth position, now how many characters do we want to replace? One, but we can replace it with more than one.

In other words we provide new text. So if we simply want X here, double quote, X, double quote, control, enter will fix it here, we see what's happened. So in the fifth position, look at the original data. 59795, now we have 5979X right here, so we see what's happened. And if we had wanted to replace this with two Xs, well we'll put in two Xs there, or XY to make it stand out a little bit better maybe, make that change and what have we done? We replaced the fifth position, down here for example, 7419034, instead of the 9034, now we've got 9XY34.

Once again as we look at the data this way. And another example over here. Pretty much same idea based on this, but we are replacing the eighth character with some text and if it is text, of course we must use quotes. So equal replace, left parenthesis, here's the data we're looking at, we're starting in the eighth position, we're gonna replace one character, but we can certainly replace it with more than one, double quote, USA, double quote, enter. So different ways of manipulating data. The examples here are all about code numbers, that's the most common use that I've seen of these functions, but there are certainly other possibilities for using them as well too.

And remember that oddity, the replace command sequence by way of the home tab, find and select, replace, is very much, not like the replace function, but it's very much like the substitute function. And remember that the substitute function has that added capability of saying, I want just the first occurance or maybe the third or the second or other occurrence of a particular character to be replaced by other characters as we saw in the examples here.