Monday Morning Mulling: September 2021 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.

.kcab emocleW

The Challenge

Thanks once again to Abbott Katz who brought this challenge to our attention.  The Microsoft Research Blog discussed LAMBDA at the beginning of the year.  They highlighted the power of this new Office 365 (Beta) function with this particular example:  

Aside from the fact that there is a mistake in the formula for REVERSE, having one too many closed brackets (they really should use FORMULATEXT!), the Research Team has erroneously stated that “…reversing a string is beyond the built-in functions of Excel and could only previously (sic) be written outside the formula language…”.

Our reader – and indeed us too – humbly begged to differ.

So this month’s challenge was as follows:

For all current versions of Excel (not just Office 365, Excel on the web and Insider / Beta variants), write a formula that will reverse the text in a cell (i.e. reverse the text string).  You cannot use VBA, dynamic arrays, JavaScript, TypeScript or Office Scripts.

Suggested Solution

It’s always dangerous to state that you can’t do something in Excel.  No disrespect is intended upon the authors of the Microsoft Research article, but I have often found it to be the case that saying such a thing may demonstrate a lack of Excel vocabulary and / or imagination.  After all, necessity is often the mother of invention! 

Having stated you might need an extended function knowledge and innovation, I shall fall back on brute force and ignorance.  We are going to need several functions. 

The first one, LEN(text), gives the length of text in terms of the number of characters contained within the text string (including blank spaces), e.g.

has a value of 24, being the total number of characters in the text string, including the total number of spaces between words.  I imagine at this point you are probably not sure where I am going with this, but before I explain, perhaps I need to introduce the next function, INDIRECT, in a little more detail.

The INDIRECT Function

Excel’s INDIRECT function allows the creation of a formula by referring to the contents of the argument (cell), rather than the cell reference itself. 

The function syntax has two arguments:

1. ref_text: this is a required reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference or a reference to a cell as a text string.  If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.  If ref_text refers to another workbook (an external reference), the other workbook must be open.  If the source workbook is not open, INDIRECT again returns the #REF! error value
2. [a1]: this is optional (hence the square brackets) and represents a logical value that specifies what type of reference is contained in the cell ref_text.  If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.  If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. 

Essentially, INDIRECT works as follows:

In the above example, the formula in cell D6 (the blue cell) is

With only one argument in this function, INDIRECT assumes the A1 cell notation (e.g. the cell in the third row fourth column is cell D3).  Note that the value in cell D2 is D4, so this formula returns the value / contents of cell D4, i.e. 77. 

This idea can be extended: the value indirectly referred to does not need to be in the same worksheet (or even workbook), or may even be of another type altogether.

Returning to the Suggested Solution

Let’s consider =INDIRECT(1).  This would return #REF! because the intermediary reference 1 is meaningless in Excel.  Yes, it constitutes a value, but INDIRECT is looking for a cell / range reference.  You might think =INDIRECT(1:3) would reference rows 1 to 3 in Excel, but you would be wrong:

This is incorrect syntax. It needs to be =INDIRECT(“1:3”) because INDIRECT is expecting to convert text to a reference it can resolve, viz.

Hang on a minute: this is spilling – we are using dynamic arrays, and these are not allowed for the purposes of the challenge.  Well, yes and no; you may use =INDIRECT(“1:3”) in all current versions of Excel, it’s just this formula cannot be rendered in all versions.  But that’s not what we want in any case.

Now, let’s extend the idea.  Let’s consider =ROW(INDIRECT(“1:3”)):

I have deliberately changed the occasional values in rows 1 to 3 to demonstrate that this has nothing to do whatsoever with the contents of rows 1 to 3. It’s generating the numbers 1, 2 and 3 as ROW(1) equals 1, ROW(2) equals 2 and ROW(3) equals 3. The ROW function has essentially passed the range argument “1:3” to whatever function or operation it has been set up for.

Just for completion and in case you are wondering, I cannot use COLUMN:

This would always extend to 16,384 columns (!).

If I combine this result with LEN from earlier,

I would get

(The & operator simply joins arguments together.)

As I say, the list of the numbers 1 to 24 might not display in all versions of Excel, but the calculation engine would recognise the list. I can even reverse the order:

The formula

takes the list generated earlier (ROW(INDIRECT("1:"&LEN(A1)))) and subtracts it from the length of the text string in cell A1 and adds one [1].  The first number would be 24 – 1 + 1 = 24, the second would be 24 – 2 + 1 = 23, the third would be 24 – 3 + 1 = 22, and so on.

Now I am ready to introduce my next function.

The MID Function

The MID function returns a specific number of characters from a text string, starting at the position you cite, based upon the number of characters you specify.

The MID function employs the following syntax to operate:

The MID function has the following arguments:

• text: this is required and represents the text string that contains the characters you want to extract
• start_number: this is also required and specifies the position of the first character you want to extract from text.  The first character in text has start_number 1, and so on
• number_of_characters: this argument is mandatory too and specifies the number of characters you want MID to return from the text.       

It should be further noted that:

• if start_number is greater than the length of text, MID returns "" (empty text)
• if start_number is less than the length of text, but start_number plus number_of_characters exceeds the length of text, MID returns the characters up to the end of text
• if start_number is less than 1, MID returns the #VALUE! error value
• if number_of_characters is negative, MID returns the #VALUE! error value.

As an example,

would return the text string “Little”, being the sub-text string starting at the 12^th character, consisting of six [6] characters.

Returning to the Suggested Solution

It starts to come together now.  Consider

in our above example:

Each cell takes the nth character in the reverse list from the text string in cell A1. We simply need to join it together.

The CONCAT function replaces the old CONCATENATE function and combines the text from multiple ranges and / or text strings:

where text1 is the text item to be joined and text2 (onwards) are the additional items to be joined.

This is why you don’t need to worry about spilling: we do not require our result to spill, and we are not using any dynamic array functions – as per the requirements.

The attached Excel file provides this example, and also shows alternative solutions using dynamic arrays and even a LAMBDA function (or three).

Word to the Wise

In some versions of Excel, you may need to enter this formula using CTRL + SHIFT + ENTER, but even with older versions, this requirement should be unnecessary.

Until next month!

The Final Friday Fix will return on Friday 24 September 2021 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.