Formula Forensics 042: Reverse Text – A Formulaic Solution
Over the xmas break I finally found the time to install Office 365.
One 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 single celled 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 either helper cells or vba
This post looks at the construction of such a function.
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:
Assuming you have the text Chandoo.org in cell B2, Excel will return
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:
Now we simply send the array into the Textjoin() function
Unfortunately the Textjoin() function is only available in the Office 365/Excel 2016 version of Excel and so this will not work in previous versions of Excel.
It would be wonderful of the nice folk at Microsoft to add this and other new functions into future Excel 2013 and Excel 2016 updates!
You can download a copy of the above file and follow along, Download Here.
Formula Forensics “The Series”
This is the 45th post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensics Needs Your Help
I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Merry Christmas & Happy New Year 2017 [Holiday Greeting]||Untrimmable Spaces – Excel Formula »|