How to Reverse Text in Excel without VBA

Discussion in 'Microsoft Excel Worksheet Functions' started by Sheikh Saadi, Sep 2, 2009.

  1. Sheikh Saadi

    Sheikh Saadi Guest

    Hi,

    i need to reverce text written in a cell without using VBA. I know how to
    write a simple VBA function to do this job, but wanted to know if there is
    any function available to do this, or even a combination of functions if
    there are any.

    A quick response would be much appriciated...

    thanks...
    --
    Sheikh Saadi
     
    Sheikh Saadi, Sep 2, 2009
    #1
    1. Advertisements

  2. Sheikh Saadi

    Sean Timmons Guest

    Reverse as in take each character and put in opposite order So abc would be
    cba?


    "Sheikh Saadi" wrote:

    > Hi,
    >
    > i need to reverce text written in a cell without using VBA. I know how to
    > write a simple VBA function to do this job, but wanted to know if there is
    > any function available to do this, or even a combination of functions if
    > there are any.
    >
    > A quick response would be much appriciated...
    >
    > thanks...
    > --
    > Sheikh Saadi
     
    Sean Timmons, Sep 2, 2009
    #2
    1. Advertisements

  3. Sheikh Saadi

    Sheikh Saadi Guest

    that's corrat... excatly like that...

    Example:
    Hello Abc --->>> cbA olleH

    is there any function or combination of funcitons?

    --
    Sheikh Saadi


    "Sean Timmons" wrote:

    > Reverse as in take each character and put in opposite order So abc would be
    > cba?
    >
    >
    > "Sheikh Saadi" wrote:
    >
    > > Hi,
    > >
    > > i need to reverce text written in a cell without using VBA. I know how to
    > > write a simple VBA function to do this job, but wanted to know if there is
    > > any function available to do this, or even a combination of functions if
    > > there are any.
    > >
    > > A quick response would be much appriciated...
    > >
    > > thanks...
    > > --
    > > Sheikh Saadi
     
    Sheikh Saadi, Sep 2, 2009
    #3
  4. Sheikh Saadi

    Pete_UK Guest

    Well, this is clumsy, but it will do it for up to 12 characters:

    =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))&
    IF(LEN(A1)<6,"",MID(A1,LEN(A1)-5,1))&
    IF(LEN(A1)<7,"",MID(A1,LEN(A1)-6,1))&
    IF(LEN(A1)<8,"",MID(A1,LEN(A1)-7,1))&
    IF(LEN(A1)<9,"",MID(A1,LEN(A1)-8,1))&
    IF(LEN(A1)<10,"",MID(A1,LEN(A1)-9,1))&
    IF(LEN(A1)<11,"",MID(A1,LEN(A1)-10,1))&
    IF(LEN(A1)<12,"",MID(A1,LEN(A1)-11,1))

    I've manually split it so that you can see how to extend it for more
    characters, but it is all one formula, assuming the text to reverse is
    in A1.

    Hope this helps.

    Pete


    On Sep 2, 10:13 pm, Sheikh Saadi <> wrote:
    > that's corrat... excatly like that...
    >
    > Example:
    > Hello Abc --->>>   cbA olleH
    >
    > is there any function or combination of funcitons?
    >
    > --
    > Sheikh Saadi
    >
    >
    >
    > "Sean Timmons" wrote:
    > > Reverse as in take each character and put in opposite order So abc would be
    > > cba?

    >
    > > "Sheikh Saadi" wrote:

    >
    > > > Hi,

    >
    > > > i need to reverce text written in a cell without using VBA. I know how to
    > > > write a simple VBA function to do this job, but wanted to know if there is
    > > > any function available to do this, or even a combination of functionsif
    > > > there are any.

    >
    > > > A quick response would be much appriciated...

    >
    > > > thanks...
    > > > --
    > > > Sheikh Saadi- Hide quoted text -

    >
    > - Show quoted text -
     
    Pete_UK, Sep 2, 2009
    #4
  5. Sheikh Saadi

    Harlan Grove Guest

    Sheikh Saadi <> wrote...
    >that's corrat... excatly like that...
    >
    >Example:
    >Hello Abc --->>>   cbA olleH
    >
    >is there any function or combination of funcitons?


    Not generally, no. Brute force is the only thing that comes close.

    With FOOBAR in cell X99, the formula

    =LEFT(RIGHT(X99,1)&LEFT(RIGHT(X99,2))&LEFT(RIGHT(X99,3))
    &LEFT(RIGHT(X99,4))&LEFT(RIGHT(X99,5))&LEFT(RIGHT(X99,6))
    &LEFT(RIGHT(X99,7))&LEFT(RIGHT(X99,8))&LEFT(RIGHT(X99,9))
    &LEFT(RIGHT(X99,10))&LEFT(RIGHT(X99,11))&LEFT(RIGHT(X99,12))
    &LEFT(RIGHT(X99,13))&LEFT(RIGHT(X99,14))&LEFT(RIGHT(X99,15))
    &LEFT(RIGHT(X99,16)),LEN(X99))

    returns RABOOF. You could extend this further, but it'll run up
    against the formula character limit, which means you can't reliably
    reverse strings longer than 50 characters or so.
     
    Harlan Grove, Sep 2, 2009
    #5
  6. Sheikh Saadi

    T. Valko Guest

    There is an add-in available that has functions that will do this.

    However, if you go to the trouble of installing an add-in you may as well
    use your own UDF.

    --
    Biff
    Microsoft Excel MVP


    "Sheikh Saadi" <> wrote in message
    news:...
    > Hi,
    >
    > i need to reverce text written in a cell without using VBA. I know how to
    > write a simple VBA function to do this job, but wanted to know if there is
    > any function available to do this, or even a combination of functions if
    > there are any.
    >
    > A quick response would be much appriciated...
    >
    > thanks...
    > --
    > Sheikh Saadi
     
    T. Valko, Sep 2, 2009
    #6
  7. Sheikh Saadi

    Sheikh Saadi Guest

    Thanks Pete, but I know this workaounrd. also, this would not work as i dont
    know if the characters are exactly 12 or less everytime.

    As far as i found out, there is no build in function for this task.

    Thanks for your response though.

    --
    Sheikh Saadi


    "Sheikh Saadi" wrote:

    > Hi,
    >
    > i need to reverce text written in a cell without using VBA. I know how to
    > write a simple VBA function to do this job, but wanted to know if there is
    > any function available to do this, or even a combination of functions if
    > there are any.
    >
    > A quick response would be much appriciated...
    >
    > thanks...
    > --
    > Sheikh Saadi
     
    Sheikh Saadi, Sep 2, 2009
    #7
  8. Sheikh Saadi

    Pete_UK Guest

    I posted it so that you can easily see the structure of the formula,
    to extend it if you need to. Just keep copying the IFs and change the
    numbers in sequence. This will do the complete alphabet:

    =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))&IF(LEN(A1)
    <6,"",MID(A1,LEN(A1)-5,1))&IF(LEN(A1)<7,"",MID(A1,LEN(A1)-6,1))&IF(LEN
    (A1)<8,"",MID(A1,LEN(A1)-7,1))&IF(LEN(A1)<9,"",MID(A1,LEN(A1)-8,1))&IF
    (LEN(A1)<10,"",MID(A1,LEN(A1)-9,1))&IF(LEN(A1)<11,"",MID(A1,LEN
    (A1)-10,1))&IF(LEN(A1)<12,"",MID(A1,LEN(A1)-11,1))&IF(LEN(A1)<13,"",MID
    (A1,LEN(A1)-12,1))&IF(LEN(A1)<14,"",MID(A1,LEN(A1)-13,1))&IF(LEN(A1)
    <15,"",MID(A1,LEN(A1)-14,1))&IF(LEN(A1)<16,"",MID(A1,LEN(A1)-15,1))&IF
    (LEN(A1)<17,"",MID(A1,LEN(A1)-16,1))&IF(LEN(A1)<18,"",MID(A1,LEN
    (A1)-17,1))&IF(LEN(A1)<19,"",MID(A1,LEN(A1)-18,1))&IF(LEN(A1)<20,"",MID
    (A1,LEN(A1)-19,1))&IF(LEN(A1)<21,"",MID(A1,LEN(A1)-20,1))&IF(LEN(A1)
    <22,"",MID(A1,LEN(A1)-21,1))&IF(LEN(A1)<23,"",MID(A1,LEN(A1)-22,1))&IF
    (LEN(A1)<24,"",MID(A1,LEN(A1)-23,1))&IF(LEN(A1)<25,"",MID(A1,LEN
    (A1)-24,1))&IF(LEN(A1)<26,"",MID(A1,LEN(A1)-25,1))

    So that:

    abcdefghijklmnopqrstuvwxyz

    in A1 becomes:

    zyxwvutsrqponmlkjihgfedcba

    But it's not very nice !!

    Pete



    On Sep 2, 11:38 pm, Sheikh Saadi <> wrote:
    > Thanks Pete, but I know this workaounrd. also, this would not work as i dont
    > know if the characters are exactly 12 or less everytime.
    >
    > As far as i found out, there is no build in function for this task.
    >
    > Thanks for your response though.
    >
    > --
    > Sheikh Saadi
    >
    >
    >
    > "Sheikh Saadi" wrote:
    > > Hi,

    >
    > > i need to reverce text written in a cell without using VBA. I know how to
    > > write a simple VBA function to do this job, but wanted to know if thereis
    > > any function available to do this, or even a combination of functions if
    > > there are any.

    >
    > > A quick response would be much appriciated...

    >
    > > thanks...
    > > --
    > > Sheikh Saadi- Hide quoted text -

    >
    > - Show quoted text -
     
    Pete_UK, Sep 2, 2009
    #8
  9. Hi,

    You may download and install the addin from
    http://xcell05.free.fr/morefunc/english/index.htm and then use the
    TEXTREVERSE(cell_ref) function

    --
    Regards,

    Ashish Mathur
    Microsoft Excel MVP
    www.ashishmathur.com

    "Sheikh Saadi" <> wrote in message
    news:...
    > Hi,
    >
    > i need to reverce text written in a cell without using VBA. I know how to
    > write a simple VBA function to do this job, but wanted to know if there is
    > any function available to do this, or even a combination of functions if
    > there are any.
    >
    > A quick response would be much appriciated...
    >
    > thanks...
    > --
    > Sheikh Saadi
     
    Ashish Mathur, Sep 3, 2009
    #9
  10. Sheikh Saadi

    mfripp

    Joined:
    Jan 10, 2013
    Messages:
    1
    Likes Received:
    0
    Here is a relatively simple formula to reverse a string up to 20 characters long in cell A1:

    =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,1,1)

    You can extend this to allow for longer strings by copying the 19-10 range, pasting it at the start, and changing 19 to 29, 18 to 28, etc.

    Note that this takes advantage of the fact that the MID() function in Excel (at least the 2011 version for Mac) will cheerfully return an empty string if you ask for a segment beyond the end of the string.
     
    Last edited: Jan 10, 2013
    mfripp, Jan 10, 2013
    #10
  11. Sheikh Saadi

    dididi

    Joined:
    Jan 29, 2013
    Messages:
    1
    Likes Received:
    0
    a way to avoid very long formulas and limitations:
    B1: =MID($A$1;B2;1) B2: 1
    C1: =MID($A$1;C2;1)&B1 C2:=B2+1
    then drag as long as you want.

    of course you may hide it on a new sheet.
    The result is in the last column of line 1.
     
    dididi, Jan 29, 2013
    #11
  12. Sheikh Saadi

    chuff5202

    Joined:
    Feb 14, 2013
    Messages:
    1
    Likes Received:
    0
    Try "=MID(A1,4-ROW(1:3),1)", substituting the length of the string + 1 for the number 4 and the length of the string for the number 3.
     
    chuff5202, Feb 14, 2013
    #12
  13. Sheikh Saadi

    jcristin

    Joined:
    Mar 27, 2013
    Messages:
    1
    Likes Received:
    0
    Hi,

    A simple solution is to create a small recursive function like this:

    Public Function RevText(s As String)
    If Len(s) = 1 Then
    RevText = s
    Else
    RevText = RevText(Mid(s, 2, 99)) & Left(s, 1)
    End If
    End Function

    Have fun!
    Jorge
     
    jcristin, Mar 27, 2013
    #13
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. PJ Karaffa

    Matrix Reverse with row identification to reverse bas

    PJ Karaffa, Feb 1, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    482
    Harlan Grove
    Feb 3, 2004
  2. Guest

    How can I reverse text in a single cell?

    Guest, Oct 12, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    290
    Aamir Hashmi
    Oct 13, 2004
  3. Guest

    How to Reverse Contents in an Excel cell (Text or a Number)

    Guest, May 20, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    18,372
    Gord Dibben
    Sep 13, 2007
  4. tom mcdonald

    reverse text

    tom mcdonald, Sep 30, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    217
    Guest
    Oct 2, 2005
  5. Guest

    REVERSE SINGLE CELL TEXT STRING

    Guest, Oct 23, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    248
Loading...

Share This Page