1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to reverse a string in Excel

Discussion in 'Ask an Excel Question' started by Jagdev Singh, May 27, 2015.

  1. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    I am ever of VBA code

    Function REVERSE(Str As String) As String

    REVERSE = StrReverse(Trim(Str))

    End Function

    Say for Jaggi –

    I have an excel formula – Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5, but I have to keep on changing it depend upon the length of the string. Is there any other easy formula in excel for this.

    Regards,

    JD
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,383
    Hi JD,

    No, there is no native function in XL that would reverse. My recommendation would be to go with the UDF you have already.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,692
    JD

    This is the holy grail of Excel Functions

    If you can crack it without using VBA or helper cells you may well go down in history
    Deepak, Khalid NGO, Asheesh and 2 others like this.
  4. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Luke and Hui

    This Question just piched by one of my colleague and I was trying to find a way around in Excel. Thanks for sharing your valuable input on it. Even I tried many permution and combination, I would like to rest this query in piece now.:):)

    Regards,
    JD
    David Evans likes this.
  5. jayalaxmi

    jayalaxmi Active Member

    Messages:
    229
    try this jaggu
    IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))
    Khalid NGO likes this.
  6. Deepak

    Deepak Excel Ninja

    Messages:
    2,875
    Hi Jaya,

    It's nice that u have taken penalty in this but what u have posted that is limited only to 5 chars & it can't be dynamic in any regards.

    One have to change the same upon increasing the chars.
  7. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Jaya

    Even in my orginal post I have a excel formula - Left(Right(A1,1),1)&Left(Right(A1,2),1)…. Till 5


    @Deepak mentioned it should be dynamic. That is the issue.

    Regards,
    JD
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,692
    JD

    Yes we can hard code a reverse string formula for a known string length

    But as yet there is no method of reversing an unknown length string without VBA
  9. Deepak

    Deepak Excel Ninja

    Messages:
    2,875
    @ All

    The main reason why we don't have a subjected solution is that excel doesn't have a native CONCATENATE which is helpful in array functions.
  10. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi All

    Thanks for your valuable input on this thread. Really Appreciated!

    Regards,
    JD
  11. David Evans

    David Evans Active Member

    Messages:
    650
    Please continue the Pursuit of Hui's Holy Grail - it could be a movie some day. We can cast Hui as the Oracle and Chandoo as the young upstart ... :cool:
    jayalaxmi and Khalid NGO like this.
  12. John()

    John() Member

    Messages:
    62
    Nice I like this thread so thought id see how i would do it ... if i had to do it and hadnt access to VBA code i could depending on how many characters you can use in a formula make it dynamic up to string length of 255 but not going to try as it would be a massive formula and not sure that size of the formula would be allowed by excel.
    So how would i make it dynamic ..... would use the choose function as this can ues 255 possibilities i believe
    so would use = choose(len(A1),and formula to change here, here, here, ... so in theory could have it reverse a string up to 255 characters in length
    as len(A1) would give length of string and resulting number would be used in the choose formula to pick the fromula at that location in the choose function ...
    have supplied a spread sheet which uses it to reverse a cell contents up to 7 characthers dynamically at moment ... so whether you type in 3 or 7 characthers it will reverse it .... might if i have the patience keep adding to it and see how many characthers i can get it up to.

    LOL in theory you could if excel accepted it, get unlimited size string .... eg if(len(A1)<=255,Choose(formula with 255 choices), then add another if with choose and so on ...... tried it by splitting my formula into two sections with a if less or =3 for one section and in false put other section.... not of sheet i uploaded though as i tried it after i posted ...... but somehow i dont think excell would allow a formula the size needed

    Attached Files:

    Last edited: May 29, 2015
  13. manish_1112

    manish_1112 New Member

    Messages:
    14
    Why not try kutools. It has simple "reverse text" addon
  14. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,692
    Manish
    It probably does just what the REVERSE(Str As String) function does but won't be transportable to others without the addin
    manish_1112 likes this.
  15. John()

    John() Member

    Messages:
    62
    Ok .... had to see max char count i could get to using the choose function .... so got it dynamic up to 35 characters ..... which needed a formula of length 7,867 ... max allowed by excel is 8,192 ....

    Attached Files:

  16. gjw

    gjw New Member

    Messages:
    1
    assuming your string is in A1, the following array function does the trick:
    {=TEXTJOIN("",TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1)))*-1+LEN(A1)+1,1))}
    (don't enter the curly brackets, use control-shift-enter instead)

    to break it down, assuming "abc" in A1:
    INDIRECT("1:"&LEN(A1)) returns 1:3
    ROW(1:3) returns array {1,2,3}
    {1,2,3}*-1+LEN(A1)+1 subtracts each element from 4 to reverse the array: {3,2,1}
    MID(A1,{3,2,1},1) creates array {"c";"b";"a"} by starting at location 3 and returning 1 character, then location 2 and returning 1 character,...
    TEXTJOIN("",TRUE,{"c";"b";"a"}) concatenates the array back to a string with a null delimiter
  17. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,692
  18. deciog

    deciog Active Member

    Messages:
    121
    If I understand you can use it this way, check it out.

    Decio

    Attached Files:

Share This Page