Final Friday Fix: October 2022 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

How do you usually find and replace a character in a text string in Excel?  Well, many people use the ‘Find & Replace’ functionality in Excel, which works fine for single replacements.  However, if you have tens, hundreds or thousands of different items to replace this method seems very tedious and inefficient.  Luckily, there are several ways to do the mass replacement in Excel more elegantly and effectively.  Therefore, in this month’s challenge we challenge you to do that, you can download the question file here.

This month’s challenge is to write a formula to replace multiple letters in some text strings.  The replacement table (here, called Replace) might be as follows:

The result should look similar to the Table Texts(below):

As always, there are some requirements:

• the formula needs to be within just one column (no “helper” cells)
• this is a formula challenge; no Power Query / Get & Transform or VBA
• the formula should be dynamic enough to update when a similar text string is added
• only Excel 2016 functions are accepted.

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