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.
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.
- =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
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:
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.
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.