From the course: Excel Formulas and Functions Quick Tips

Unlock this course with a free trial

Join today to access over 24,800 courses taught by industry experts.

Change text with SUBSTITUTE and REPLACE

Change text with SUBSTITUTE and REPLACE - Microsoft Excel Tutorial

From the course: Excel Formulas and Functions Quick Tips

Start my 1-month free trial Buy for my team

Change text with SUBSTITUTE and REPLACE

“

- [Narrator] The SUBSTITUTE function replaces text based on the content of the text. Everything that's 1099 in this column, I'd like to change to 1199. I'll use SUBSTITUTE. I'll select my range, put in a comma, add my old text, include another comma, and then put in my new text. I'll close the parentheses and everywhere there was a 1099 is now an 1199. Everything else remains unchanged. It can also replace specific instances of text. I need to update this last 20 with a 24, but I don't want to change both of them. I'll put in the cell, the old text, the new text, and now I'll use an optional parameter called instance number. I only want to change the second instance of a 20. We can also accomplish this using the REPLACE function. The REPLACE function changes text based on its position in the string. Once again, we're testing cell A2. This time, I'm replacing text seven positions over. I'm replacing two characters and I'm replacing them with the characters 23.

Contents