Find & Replace multiple words in Excel | REDUCE & SUBSTITUTE | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
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, in this video, I want to share how to Find and Replace multiple words in Excel using the REDUCE and SUBSTITUTE functions.

0:00 Introduction
0:26 Using REDUCE() and SUBSTITUTE()
5:58 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[petercompton]

This is great - good to see a practical use for REDUCE, which is not one of the easier Lambda functions to get one's head around!

[serdip]

Thanks for posting another very interesting and practical tutorial in Excel. Since I started learning Power Query a scant three weeks ago, I am trying to use it in as many situations as possible.😃 I tried to replicate the functionality demonstrated in the video using PQ. I made a custom function that uses List.Accumulate() to make successive substitutions of the placeholders in the input string with the corresponding replacement values as listed in the table, conceptually mimicking (I think) the Excel REDUCE() function that you explained how to use.

As I am far from proficient in PQ M code, my custom function is probably horribly inefficient and inelegantly composed, But, based on my very limited testing, it does seem to work. If nothing else, I had fun attempting to write some custom M code! Thank you kindly.

// fxDynamicText()
// Replace placeholders in input text, delimited by {, and },
// with values from the FindReplace table.

(input as text) =>
let
// From the FindReplace table, create a list of lists {<find value>, <replace value>}
// Have to case each element of Mapping as a list itself before using it in the
// accumulator function.
Mapping = Table.ToList(FindReplace, Combiner.CombineTextByDelimiter(", ")),
// Transform each element of Mapping into a list because initially each
// element is just a text string with no structure. We require an indexed
// sequence of items, so we can replace occurrences of the first element
// with the value in the second element.
Mapping_List = List.Transform(Mapping, each Text.Split(_, ", ")),
// The "seed" (2nd argument) is the original input string containing placeholders for
// dynamic text substitution.
Source = List.Accumulate(Mapping_List, input, (state, current) => Text.Replace(state, current{0}, current{1}))
in
Source

My input string was:
"My {sentence} has {placeholders} with {dynamic_content}. It is {month} {year} and I'm enjoying learning about {subject} on the {youtube channel} YouTube channel."

My output string was:
"My office has a computer with Excel 365. It is July 2023 and I'm enjoying learning about Power Query on the Excel Off The Grid YouTube channel." 😎

[IvanCortinas_ES]

Superb job as always Mark. Absolutely clear and bright. Thank you!!!

[Lanefasts]

Mark, this solution is perfect for my application because I have a long list of standard e-mail replies for my client's FAQ's. When I import a customer's information into the array, the generated replies include their specific name and order information. From there, all I have to do is copy/paste the generated text into my email reply instead of re-typing the same message over and over, and there are no worries of misspelling or entering a number incorrectly. MASSIVE time saver for me, the efficiency is off the charts. I was using nested SUBSTITUTE functions until now, but the formula has become too long and tedious to update, and feels like a bad workaround. I was looking for a cleaner and more dynamic formula, and the one you presented in this video is exactly what I've been looking for! Thank you!!!

[gnsarathbabu]

How can we do that in powerquery only if two columns are matching from two tables. Then find and replace only for those matching columns

[JohnWickXL]

This is great, thank you for sharing. Do you know if it's possible to replace the 'Offset' function within this formula? I try to avoid volatile functions where possible. I attempted to use 'Index' instead but that didn't work. I think there's no alternative but thought I'd ask!

[Axel-MannaRecords]

it gives the "#value!" error . Any idea why ? I have replicated exactly your table . my office version : 16.78 . Microsoft 360

[williamarthur]

That was really cleaver, it's quite hard to break down and visualize the gradual accumulation.

[GumMice]

This is really cool but I am struggling to find a practical example of when one would realistically use this. Any ideas?