Formula Forensics 042: Reverse Text – A Formulaic Solution

Over the xmas break I finally found the time to install Office 365.

On of the new functions that Office 365/Excel 2016 allows is the Textjoin() function.

Textjoin takes an array or range of text and joins them together with an optional separator character

eg: =Textjoin(“,”,True,”C”,”h”,”a”,”n”,”d”,”o”,”o”,””,”.”,”o”,”r”,”g”) will return Chandoo.org

 

The use of Textjoin allows for a formulaic solution to the previously impossible Reverse Text problem, as yet unsolved in previous versions of Excel without using VBA.

That is there was no way to reverse a string of text in Excel without using vba

This post looks at the construction of such a function.

 

Textjoin()

The Textjoin() function has 3 components as shown in the Excel Help

How to Reverse Text

Previously in Formula Forensics we have used a simple formula to extract each character of a text string into an array using:

=MID(B2,ROW(INDIRECT(“1:”&LEN($B$2))),1)

Assuming you have the text Chandoo.org in cell B2, Excel will return

={“C”;”h”;”a”;”n”;”d”;”o”;”o”;”.”;”o”;”r”;”g”}

so each character takes up a single location within the array

We can reverse the order of the array by changing the location of the character during extraction so that instead of taking characters from left to right we take them right to left, by using a small modification to the above formula:

=MID(B2,LEN(B2)-ROW(INDIRECT(“1:”&LEN($B$2)))+1,1)

Excel returns

={“g”;”r”;”o”;”.”;”o”;”o”;”d”;”n”;”a”;”h”;”C”}

Now we simply send the array into the Textjoin() function

=TEXTJOIN(“”,TRUE,MID(B2,LEN(B2)-ROW(INDIRECT(“1:”&LEN($B$2)))+1,1))

Excel returns

gro.oodnahC

 

Limitations