# Achieving the Impossible: Reversing Text

I would like to discuss a key issue frequently encountered in Excel – the supposed “it’s impossible to do in Excel” problem. This is often decreed, but seldom proves to be the case, as is demonstrated in this claim.

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, 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…”.

This is not true. Even if you haven’t access to **LAMBDA** and several other Excel 365-specific goodies, don’t worry – you *can* keep reading!

Consider the following:

For **all** current versions of Excel (not just Office 365, Excel on the web and Insider / Beta variants), I wish to write a formula that will reverse the text in a cell (*i.e.* reverse the text string). Since I want it to work “everywhere”, I cannot use VBA, dynamic arrays, JavaScript, TypeScript or Office Scripts.

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 you may do just about *anything* in Excel with a little experience and, ahem, “imagination”. After all, necessity is often the mother of invention!

Having stated you might need an extended knowledge of functions and some innovation, it’s amazing what may be achieved with a little brute force and ignorance. We are still going to need several functions though...

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.*

**=LEN("Mary Had a Little LAMBDA")**

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.

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.

**INDIRECT(ref_text, [a1])**

The function syntax has two arguments:

**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**[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

**=INDIRECT(D2)**.

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.

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 this won’t work in all versions of Excel. 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,

**=ROW(INDIRECT("1:"&LEN("Mary Had a Little LAMBDA")))**

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

**=LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1**

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 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:

**MID(text, start_number, number_of_characters)**

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,

**=MID(“Mary Had a Little LAMBDA”,12,6)**

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

It starts to come together now. Consider

**=MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)**

in our above example:

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

**=CONCAT(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))**

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

**CONCAT(text1, [text2],…)**

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.

The associated 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.