Excel SUBSTITUTE Function: Teachers Out Today

The Excel SUBSTITUTE Function is another Text manipulation function that will replace all characters in the given string of text with the characters in the specified cell. 


Description of the Excel SUBSTITUTE Function

The Excel SUBSTITUTE Function replaces any text in a string of text, with the given text value.


Excel SUBSTITUTE Function:

Syntax of the Excel SUBSTITUTE Function:

  • SUBSTITUTE(text, old_text, new_text, [instance_num])
  • text (Required) - The text or the reference to a cell containing text for which you want to substitute characters.
  • old_text (Required) - The text you want to replace.
  • new_text (Required) - The text you want to replace old_text with.
  • instance_num (Optional) - Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples:

  • =SUBSTITUTE("Hello Hello", "Hello", "Goodbye") = Goodbye Goodbye
  • =SUBSTITUTE("Hello Hello", "Hello", "Goodbye", 1) = Goodbye Hello
  • =SUBSTITUTE("Hello Hello", "Hello", "Goodbye", 2) = Hello Goodbye
  • =SUBSTITUTE("1-800-999-9999", "-", ".") = 1.800.999.9999
Excel SUBSTITUTE Function

I Tried The Fancy Telephone One But It Didn't Work!

Wellllllllll..., it actually Did work. The formatting of the cell just isn't quite right.

In order to display the string of numbers as a Phone Number, you first need to format the cell correctly. You do that by Right-Clicking on the cell, containing the formula, and choosing Format Cells. On the Number Tab, choose Special from the Category list on the left and then choose Phone Number from the Type section on the right and then click the OK button.

BOOM! You Haz Yoself A Fone Numba!

Why Are You Adding A ZERO​ To The Formula?

​The Excel SUBSTITUTE Function is strictly used for Text manipulation, so all results from this function are treated and returned as text. By adding the Zero to the end, Excel automatically converts the text to an actual number, but doesn't change its value. Now that it is a true number, the Phone Number format will display it in the fancy format, as shown in the spreadsheet.

If you remove the +0 from the formula, the cell will revert back to a string of numbers. Go ahead and try it, you won't hurt anything.


Download The Example Spreadsheet Here:

The Excel SUBSTITUTE Function


Why use the Excel SUBSTITUTE Function?

Sometimes you may realize that you should have used different words, symbols or numbers in your cells but you just don't have the time to go through every cell and update them manually. This function makes that job extremely easy and saves you a ton of time too. As with all of the other Excel Text Functions, nesting these function inside of each other makes for some really powerful stuff. Add this function to your tool chest and impress your Boss on how fast you can make the necessary changes.

Conclusion

​This function will help to make you a more efficient Excel User. The better you are, the more valuable you will be to those that employ you. There simply is no Substitute for learning as many Excel Functions as you can and this one will prove useful over and over again.

Share, Comment, Ask a Question or Rip Me a New One!

If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!

Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services​.
Joe Austin
 

Joe Austin is the founder of Potentials Unleashed. He is currently an IT Professional Manager in the Financial Industry and has been using computers since 1978. While mostly self-taught, he is proficient in several computing areas, such as Programming , Web Development, most Microsoft Office programs, Internet Marketing, Spyware / Malware Removal and even 3D Animation & Modeling.

Click Here to Leave a Comment Below 0 comments
%d bloggers like this: