Please note javascript is required for full website functionality.


Final Friday Fix: July 2024 Challenge

26 July 2024

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!