As many regular readers will know, I’m a big fan of writing text dynamically. This means we don’t need to manually update text or headings in our reports. Instead, the text calculates and updates automatically. So, today I want to share how to Find and Replace multiple words in Excel using the REDUCE and SUBSTITUTE functions.
Let’s start by looking at the functions, then finding out how to use them together.
Download the example file: Click the link below to download the example file used for this post:
Watch the video
Contents
SUBSTITUTE function in Excel
The SUBSTITUTE function finds and replaces text inside another value.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance])
- text: The text to change
- old_text: The text to find
- new_text: The text to replace with
- [instance]: The nth instance of the old_text to replace. This is an optional argument. If not supplied, all instances are replaced.
Example
Here is a simple example:
=SUBSTITUTE("Flori is my dog's name","dog","cat")Every instance of dog is replaced with cat.
Result: Flori is my cat’s name
NOTE: SUBSTITUTE is case-sensitive.
REDUCE function in Excel
The REDUCE function applies a LAMBDA function for each array element, returning the accumulated result.
In simple terms, REDUCE behaves like a looping function. It performs a calculation multiple times over an item and then returns a single final result. This might not sound useful, but by the end of this post, you will see its power.
Syntax
=REDUCE([initial_value], array, lambda)
- [initial_value]: The initial value of the accumulator. This is optional, as the initial value can be blank.
- array: The array for which a LAMBDA function is applied for each element
- lambda: The function to perform for each element
Find out more about REDUCE: https://exceljet.net/functions/reduce-function
LAMBDA
The last argument of REDUCE is lambda; this is the function to perform for each item in the array.
LAMBDA is a special type of function for making custom functions. When used with REDUCE, LAMBDA has a specific syntax we need to follow. Rather than risk confusing you any further, let’s look at an example so you can see how it works.
Find and Replace Multiple Words in Excel
OK, now it’s time to use these functions.
Example data
This is the example we are working with
Cell B3 contains a template sentence. My {sentence} has {placeholders} with {dynamic content}.
Our goal is to find each word with curly brackets (e.g. {sentence}, {placeholders}, {dynamic content}) and replace it with a new word. The find and replace elements are contained in the Table called FindReplace (Cells B6:C9).
Apply the SUBSTITUTE function
Let’s start by applying the SUBSTITUTE function.
The formula in cell F3 is:
=SUBSTITUTE(B3,B7,OFFSET(B7,0,1))This is taking the value in B3, finding the value in B7, then replacing it with the value 1 column to the right of B7.
SUBSTITUTE can only include one find, and one replace value.
Using REDUCE with SUBSTITUTE
By using REDUCE, we can perform the SUBSTITUTE function for each item in the Find list.
Here is the final result:
The formula in cell F3 is:
=REDUCE(B3,FindReplace[Find],LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,0,1))))
Let’s break this down:
- B3 is the initial value
- FindReplace[Find] is the list of items to find
- a: the parameter representing the SUBSTITUTE result at the end of each loop. It will start with the value in B3, but changes each time the calculation runs.
- v: the parameter representing each element in the FindReplace[Find] array.
The first time the calculation runs, SUBSTITUTE replaces {sentence} with office. This gives a result of My office has {placeholders} with {dynamic content}.
The second time the calculation runs, SUBSTITUTE replaces {placeholders} with a computer. This gives a result of My office has a computer with {dynamic content}.
The third time the calculation runs, SUBSTITUTE replaces {dynamic content} with Excel 365. This gives the final result of My office has a computer with Excel 365.
This shows that REDUCE has applied SUBSTITUTE for each item in the FindRepalce table.
Applying the formula to your scenario
The formula above may appear confusing. However, to apply it to your scenario, there are only 2 things you need to change.
=REDUCE(B3,FindReplace[Find],LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,0,1))))- B3: Change this to be your initial value
- FindReplace[Find]: Change this to be the values to find
Provided the Replace values are directly right of the Find values, the calculation will work for your scenario.
Conclusion
REDUCE and LAMBDA give us an excellent way of performing a calculation repeatedly.
Even if a function, like SUBSTITUTE, only performs a single action, wrapping it in REDUCE and LAMBDA causes that calculation to be undertaken multiple times.
Using the REDUCE and SUBSTITUTE combination, we can find and replace multiple words in Excel to calculate dynamic sentences and headings.
Related Posts:
- How to create dynamic text in Excel (TEXT + Number Formats)
- How to remove spaces in Excel (7 simple ways)
- Automatic commentary writing formula in Excel – Amazing LAMBDA
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
How do you even think of these use cases?!
Brilliant!
I find a problem, I try to find the best way to solve it. Often it leads off on a tangent to look at things I’ve never thought of before.
Hi Mark,
Once again, a nicely explained function with unlimited potential. Imagine, if you will, using an xlookup formula to populate C7, C8, and C9. You could iterate through a long list of values making this even more valuable.
Regards,
Yes, you certainly could use XLOOKUP inside the REDUCE. I’m not sure it would give a better solution in the scenario. But now that you’ve mentioned it, I suspect there are other really good use cases… I might have to go and play.