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 replacethat can get a little confusingbecause the replace command is very similarto the substitute function, it's notsimilar 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 hereand we want to remove all dashes.We can use the function called substitute,but it's gonna be simpler and fasterto 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 replacetells 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 thiswith 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 thisI'll come back and find two underscoresand 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 functionat least for these kinds of examples.But we do need them here because the substitute functionallows us to remove or to substitute only the firstoccurrence 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 bitout of place here, but we can usesubstitute 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 atcomma item that we're actually referring towithin double quotes is a dash,that's the so called old text commathe new text that we want hereis nothing double quote, double quote,and the instance number is our last choice here,if we only wanna remove the first dashwe 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 removingthe dash just like we did beforeand replacing it with XXX,but we're only doing it for the second instanceand so, seeing here, we're gonna see384-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 nothingto do with that command where we usefind and select and replace.We will use the replace function here,and here too based on the description abovewe can replace a character or characterswith 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 charactersdo 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 XYto 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 characterwith 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 aboutcode numbers, that's the most common usethat I've seen of these functions,but there are certainly other possibilitiesfor using them as well too.

And remember that oddity,the replace command sequence by wayof 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 functionhas that added capability of saying,I want just the first occuranceor maybe the third or the secondor other occurrence of a particular characterto be replaced by other charactersas we saw in the examples here.

Resume Transcript Auto-Scroll

Author

Updated

12/6/2016

Released

1/16/2015

This tips-based course will show Excel users productivity-boosting tricks, cool hidden features, need-to-know functions, and advanced content on subjects such as using PivotTables for data analysis. Tune in every Tuesday for a new tip from expert Dennis Taylor. Each tutorial is a short, self-contained lesson guaranteed to give you new insights into Excel