Final Friday Fix: July 2024 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

This month’s challenge is to dynamically substitute text in a dataset.  Specifically, you need to replace certain keywords in a text string with their corresponding substitutes from another list.

Imagine you are given a text string with multiple occurrences of the placeholder '[e]' and a replacement table that provides different replacement values for each '[e]'.  The main goal is to create a dynamic solution that replaces each '[e]' in the text string with the corresponding value from the replacement table.  You can download the original question file here.

As always, there are some requirements:

• the formula needs to be within just one [1] column (no “helper” cells)
• you must ensure the solution is dynamic so that it updates when new placeholders or replacement values are added
• no VBA or Power Query is allowed; this is purely a formula challenge.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!