Final Friday Fix: August 2021 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you are playing for bragging rights only!

You should text your time on this one…

The Challenge

Sometimes when modelling you need to identify the location of the nth occurrence of a character in a text string, perhaps to truncate the text or to manipulate it in some other fashion.

This month’s challenge is to write a formula in one cell that will identify the nth occurrence of a character in a text string.  There are some requirements:

• the formula needs to be in just one cell (no “helper” cells)
• this is a formula challenge – no Power Query / Get & Transform or Text to Columns!
• the formula must work in all current versions of Excel (so no VBA, dynamic arrays, LAMBDA, LET or user defined functions)
• the model may be large or unstable, so no volatile functions are allowed
• the formula must be case sensitive.  For example, in the illustration above the third occurrence of “l” in “Hello Lesley” is in position 10, i.e. “Hello Lesley” – the capital “L” is ignore.

If that all seems too easy, you may supplement the challenge by locating the last occurrence in the same text string too, subject to the same restrictions.

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