Hi, this is Wayne again with a topic “Using the Excel SUBSTITUTE Function”.
In this Excel video, I will show you how to use the substitute function in Microsoft Excel. You can use it to take existing text and substitute it with new text. Now you might be saying to yourself. I already know how to do that. Can’T I just use find and replace, and in many cases the answer is yes, you can go to the Home tab here on the home ribbon in the editing group.
There should be a magnifying glass or something like it and you could go down here and click replace, and here I could type in existing text. Let’S say the word synth pop and I could choose to replace it with, let’s say synth hyphen pop and then I could go ahead and click replace all and all instances in this spreadsheet of synth pop have been changed and updated to have a hyphen in them. 32 Replacements in total. I can click, OK close this and you can see that it worked.
So why don’t? I just do that in this case. There are time times when find and replace isn’t really what you want. You may want to just change the text in a specific range or in a specific cell, so, for example, here at the left, I have a list of synth pop albums and other related genres, and you can see here that find and replace changed. Not only the list of genres, but also the names of some of the albums, this album was named not by me but by someone else, and the official name of this album does not have a hyphen in it.
So that’s not really accurate for me to go ahead and just change the title of this album that someone else produced so I’m going to undo that with Ctrl Z on the keyboard instead of using, find and replace, I’m going to use the substitute function. Step 1. In this case is, I need some space to work with, so I’m going to insert a new column between C and D I’ll.
Do that by right, clicking on column D and choosing insert now. I have that blank column and I’ll click here on C1 and use the autofill handle to just drag that over, so that genre is repeated here in column D. Next, I click on D2 and I’ll type equals substitute as soon as I start typing that you can see Excel is suggesting The Substitute function.
It says it replaces existing text with new text in a text string. That’S exactly what I want to do so I could just continue typing the word substitute, it’s kind of a long word, though, in this case – and I think I’ll just double click here on substitute and it puts in that function. For me, along with the left, parenthesis and now Excel, gives me a little pop-up that gives me hints about what Excel is expecting me to provide to it. It’S expecting me to select some text put a comma and then type in the old text. The existing text, comma and then new text and then there’s another optional thing here at the right. Let’S talk about that later, but for now I’m just going to click on C2, where I could type the cell reference C2, but either way I’m showing Excel that this is where the text is I’ll type, a comma.
What is the old text in quotation marks? I’M going to type synth pop close quote, I put in my comma Now, what’s the new text, what do I want to change this word into again? It needs to be in quotation marks I’ll type in synth hyphen pop close quote, and that’s all that’s required in this formula. I should put in my right parenthesis, but in this case I don’t really have to and then I’ll just tap enter on the keyboard and it didn’t work. Somehow, when I was typing out the formula, the cell reference C2 became G2.
So I’m not exactly sure what I did wrong there, but I’m just going to change that from G2 to C2 tap enter and it worked now. Do I need to do that same formula over and over all the way down this spreadsheet? No, fortunately, no. I can just click here on D2, where it looks like it says: synth pop with the hyphen, but actually what’s in this cell L is this formula here that you can see on the formula bar.
So, with that cell selected, I can just double click in the lower right hand, corner of the selected cell there’s an autofill handle there in this case, it’s so small, it’s hard to see, but there it is it’s just this small green square, I’m going to go Back to a more zoomed out view, I’m just going to double click on that autofill handle and look what happens. It copies that formula down the spreadsheet and it’s looking for synth pop without the hyphen and changing it into synth pop with a hyphen. Now what? If it doesn’t find the word synth pop, that’s okay, it just keeps what was there already now? What about my album? That’S called synth pop Club, albums too. That was not changed. Why? Because I used the substitute function. Excel is looking specifically in the column and the cell in this case that I put into the formula so C2.
That’S where it’s looking. It doesn’t care about column B. Now, at this point, I’m tempted just to delete column C, I don’t need both of these columns. So I could right click on C and choose delete, but look what happens it ruins column C. So I’m going to undo that and what I’m going to do to fix this issue of the fact that this column now is full of not simply text. But of formulas that produce text what I’m going to do to fix, that is I’m going to click and drag to highlight the range that I would like to take from being a formula and turn it into simple text.
So I’ve highlighted that range now I’ll just copy it in this case I’ll just go up here to the Home, tab, clipboard group and click on copy and then with that same range selected, I’m going to go here again to the same clipboard group. But this time I’ll click on the lower part of the paste button and I’ll choose paste values, so I’ve copied formulas and pasted values. So now I can delete column C and there are no issues, no errors.
Now, there’s one other option: National argument that you can use in your substitute formulas. So I’m going to insert a new tab to demonstrate this and I’m going to go in here to the name of this album by over and I’m going to change the title of this album to make it incorrect. That’S not the correct name of the album! Now, let’s see how could I fix that using the substitute function, I’m just going to click here on Cell c42, I’ll type equals substitute left parenthesis.
I select the text that I want to change, comma in quotation marks. What is the old text? It’S the word. The now keep in mind this is case sensitive, so I need to make sure that I’m typing exactly the text that I want to replace close quote put in my comma. The new text in quotation marks I’ll put the word of comma and then here is an optional argument that will help solve a problem.
You can see. The problem cell b42 has two instances of the word the which 1 do. I want to replace with the word of well in this case it’s the second instance, and so, even though this is optional in this case, I really need to use this, because I don’t want to change the first, the I want to change the second, the so I’Ll just put in a number two I should put in my right parenthesis tap enter and it worked the formula that I just created using the substitute function. It left the first, the alone, but it changed the second to B of and that’s exactly what I needed.
I can now double click on the autofill handle and the rest of this text is also corrected. If I wanted to, I could now click and drag to select the corrected text. I could then copy and then I could select the incorrect text if I wanted to, and then I could do a paste paste values only and I have now fixed the spelling in this range. So in this case I can delete column C just by right.
Clicking delete and I’ve used the substitute function to correct this mistake, so I think you’ll find that in many cases find and replace is a sufficient way to fix mistakes like this or make changes. But there are cases and situations in which using the substitute function will be better and faster for you thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified.
When I post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you’ll, see information about those options in the description below the video. Speaking of patreon, I want to give a quick shout out to my five dollar patreon supporters. Thank you. So much for all you do to support the channel.
I really appreciate your support and also the support of all of my patreon supporters, not just the five dollar level supporters. Thank you. If any of you are interested in learning about some of these great synth pop albums or bands, you can look in the description below and I’ve included some links. .