Substitute Formula
The substitute formula is an relatively simple formula that can come in handy in many situations. I like to use this formula the most when a longer text field needs just a portion of it changed, without having to retype the entire cell value. Let’s go ahead and take a look at the substitute formula:
=SUBSTITUTE(Text, Old_text, New_text, Instance_num)
Looking at each of the criteria of the field, we’ll see how this formula is formed. Text is the string or cell reference of the string you want to change. Old_text is the value inside the Text string that needs changed. New_text is the value you want to change to the Old_text value to. And finally Instance_num is only specified if you only want to change a certain occurence of the the Old_text. If you don’t specify and Instance_num, all instances of Old_text will be replace.
Alright, let’s go ahead and take a look at some examples of how the Substitute Formula works.
EXAMPLES
Example 1: =substitute(“Let’s have fun.”, “fun”, “dinner”) will return “Let’s have dinner.”, since “fun” is replaced by “dinner”.
Excel Hints is almost fixed!
I have been having numerous problems with the site lately and am finally almost finished getting everything back to working order. For a long time I was unable to upgrade my version of WordPress without it crashing, but that problem is now fixed. I have put back together all the category sections (I’m still working on the tips menu that was there before, on the right side of the site). Overall I’m pleased with the new version of WordPress and believe it will definitely help with to streamline updates and new posts to the site.
Another sidenote, if you left a comment over the last several months, it most likely did not show up on the site. I have since fixed that problem as well and approved your comment. I will be responding to all those comments shortly.
Thanks again to everyone’s patience and for sticking with Excel Hints!
John
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Macro tips on Excel Hints
Lately all the updates I have been adding to excel hints have been related to the formulas that are incorporated into Microsoft Excel. While I still probably have hundreds of formulas that need added to this site, I am going to begin adding tips on how to use the Macro capabilities for Excel.
Macros in Microsoft Excel are written in Visual Basic for Applications (or VBA), which is just a program to provides you with the ability to a little more functionality to your spreadsheets. Macros are an excellent choice to use for any repetitive tasks you need to accomplish, and the examples I will provide will make that obvious. As with the formulas added to the site, we will begin with some basic functions and build upon them.
Anyone that has any suggestions on something that they would like to see on the site or suggestions on how I can make the site better, please contact me using the contact form.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Calculate a Loan Payment in Excel
Whether you are trying to buy a new house, looking at buying a new car or applying for MORE student loans, you have no doubt wondered what your monthly payment would be. Excel provides an easy to use formula in which you can enter the loan information, and it will calculate the monthly payment for you. Once you learn to use it, it’s actually very simple. So let’s go ahead and take a look at the PMT Formula: Read More
Create a Drop Down Menu in a Cell
I recently received an email from a reader asking how to add a hidden drop down menu inside a cell. I was unable to respond back to the reader (email address was invalid), but I thought it was a great question and wanted to add it to Excel Hints. In just a couple steps, you’ll be able to add this feature to your spreadsheet and improve the validity of many spreadsheets you create. Read More
Loading ...