Formula Forensics 042: Reverse Text – A Formulaic Solution

Posted on January 4th, 2017 in Formula Forensics , Huis , Posts by Hui - 27 comments

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.

 

Textjoin()

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

tj001

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

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!

 

Download

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 Forensic Series

 

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;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

27 Responses to “Formula Forensics 042: Reverse Text – A Formulaic Solution”

  1. Michael (Micky) Avidan says:

    Just to correct a small misunderstanding.
    "Exce 365" has these (and a few more) new functions but they are not a part of "Excel 2016".
    Using the term: "Office 365/Excel 2016" is a little misleading.
    It seems as if the new functions can be found in BOTH mentioned versions which is not true.
    "Office 365" is a stand alone application and has nothing to do with the "Excel 2016".
    To my opinion the best way is to use only the term "Excel 365" as it is going to keep its name for many years and it gets a monthly update incl. new features/functions, from time to time.
    Thanks.

    • Hui... says:

      Micky

      https://support.office.com/en-us/article/What-s-the-difference-between-Office-365-and-Office-2016-ed447ebf-6060-46f9-9e90-a239bd27eb96

      "Office 365 is a subscription service that includes the most recent version of Office, which currently is Office 2016.
      It comes with the applications you’re familiar with, like Word, PowerPoint, and Excel, plus extra online storage, ongoing tech support at no extra cost, and more."

      So yes, it is Excel 2016 that I use, but it is the Office 365 version of Excel 2016. The new functions are not available in stand alone Excel 2016 versions.

      • Michael (Micky) Avidan says:

        Hui,
        With all due respect there is nothing new in your comment.
        When you mention OFFICE 365/Excel 2016 in "a single breath" this might mislead some of the readers.
        To my opinion (you may or may not agree) if you want to mention some of Excel 365 New/additional features - it will be good practice NOT TO MENTION THE YEAR 2016.
        All the above was written from the eyes of the average end user (not you nor me).
        I rest my case and have nothing more to add (at this point).

        • David Hager says:

          You make a good point, but in a poor tone, IMO. The main problem is that Microsoft marketing is about as clear as mud on this subject. BTW, I have amended several TEXTJOIN articles I wrote this week to include your viewpoint, so thanks!

          • Hui... says:

            Thanx David

            I have been using Excel 2016 for a couple of years and it was one of your posts i the Forums that reminded me of Textjoin which unfortunately 2016 doesn't include natively.

            As an MVP we get free access to a 365 Subscription which I implemented last week.

            So now I use Excel 2016 with a few new functions and facilities with the side effects of having a 365 a/c

          • Michael (Micky) Avidan says:

            @David,
            It is not a secret that English is not my mother's tongue.
            Therefore the so called "TONE" should be examined & interpreted as long as the above statement being considered.
            Thank you.

          • David Hager says:

            Thanks, Hui! I have written quite a few articles about TEXTJOIN recently. If anyone is interested, visit https://dhexcel1.wordpress.com/

          • David Hager says:

            Micky,

            Did your mother teach you to communicate in ALL CAPS?

  2. Gino says:

    Thanks for the post, Hui! I'm just struggling to understand why anyone would ever need to use this formula. The example you used makes sense from a formula approach but do you know of any um, more "real life" reasons why you would use it?

    Just seems like an odd function!

    Cheers,
    Gino

    • Michael (Micky) Avidan says:

      @Gino,
      Does the linked picture make any sense ?
      https://s28.postimg.org/p5uslp1l9/NONAME.png

      • Hui... says:

        Thanx Michael

        That explains Textjoin but probably doesn't explain why you would need to reverse text in the first place

      • Gino says:

        Yes, Micky - that really helps me understand it better. So a CONCATENATE on steroids! 🙂

        Awesome! And to all this MS stuff about differences between 365 and standalone - maybe MS way of forcing us all eventually to move to a software subscription model?

        Probably.

        Anyway - thanks, Michael (Micky)! Much appreciated!

    • Hui... says:

      @Gino

      Assume that a company has a production line making widgets
      its widgets are named A through Z

      So next weeks requirements may be listed as DCHGSDCXZX
      That is we need to make widget D then C then H then G etc widgets

      I could imagine that there is a requirement at some stage to know what is last, then second last etc, ie the list in reverse order

  3. Stef@n says:

    Hi
    or VBA 😉

    Option Explicit
    Function Mirror(z)
    Dim i As Long, l As Long, temp As String
    l = Len(z)
    For i = l To 1 Step -1
    temp = temp & Mid(z, i, 1)
    Next i
    Mirror = temp
    End Function

    Kind regards
    Stef@n

  4. John Jairo V says:

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

    I think you should be careful in the way you say it can not be done in Excel. In itself the problem is to do it with a single function, but that can be done, can be with helper cells. Blessings!

  5. Jon M. says:

    Wouldn't this formula work? I went out 20 characters, but you could go more if needed. And I admit, it's not as elegant.

    A1=Chandoo.org

    A2=MID(A1,20,1)&MID(A1,19,1)&MID(A1,18,1)&MID(A1,17,1)&MID(A1,16,1)&MID(A1,15,1)&MID(A1,14,1)&MID(A1,13,1)&MID(A1,12,1)&MID(A1,11,1)&MID(A1,10,1)&MID(A1,9,1)&MID(A1,8,1)&MID(A1,7,1)&MID(A1,6,1)&MID(A1,5,1)&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,6,1)&MID(A1,1,1)

    -Jon

    • Hui... says:

      @Jon
      That formula works if the string is 20 characters long
      If it is 19 it will have an error
      If it is 21 it will give the wrong answer

      My solution is generic and works regardless of the string length

      • Jon M. says:

        Self righteous much Hui?

        I acknowledged in my post that it max's out at 20, but it is expandable depending on the user's needs; and that it was not as elegant as yours.

        Also, my solution does NOT error if it is less than 20, try it (or just look at the formula).

        PS. Does your solution does really work regardless of string length? Is it truly free of the maximum number of characters?

  6. Leonid says:

    Another option to put together reversed characters is to use the CONCAT function that was also introduced in Excel 2016:
    =CONCAT(INDEX(MID($B$2,LEN($B$2)-ROW(INDIRECT("1:"&LEN($B$2)))+1,1),))
    or array entered
    =CONCAT(MID($B$2,LEN($B$2)-ROW(INDIRECT("1:"&LEN($B$2)))+1,1))

  7. Volkan Güven says:

    Hi,

    If it is okay for you to use third party excel add-ins; ASAP utilities and Kutools has "reverse text" options.

    Best Regards

  8. Stephen Lee says:

    Thanks for sharing! Perkful content got! Keep it up!! I am eager to wait for next writings!

Leave a Reply