Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Reversing contents of a cell

  1. #1
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all,

    I am trying to figure out a way to take the contents (character) of a cell (A1 below), and in the cell immediately next to it (B1 below), place the reversed contents of that cell.

    For example,

    A B
    1 god dog
    2 cats stac

    Any thoughts would be greatly appreciated.

    Regards,

    Sean

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Download (and add-in) morefunc.zip at http://perso.wanadoo.fr/longre/excel/downloads/, and use the following array formula...

    {=MCONCAT(MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1))}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following UDF:

    '----------------
    Function ReverseIt(MyCell)
    Dim temp, x As Integer, CellLen As Integer

    CellLen = Len(MyCell)
    For x = CellLen To 1 Step -1
    temp = temp & Mid(MyCell, x, 1)

    Next x
    ReverseIt = temp
    End Function
    '----------------

    Called as

    =ReverseIt(A1)

    for example.

    Bye,
    Jay

  4. #4
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I came up with a method to do this in a cell...

    =CONCATENATE(MID(C2,6,1), MID(C2,5,1), MID(C2,4,1), MID(C2,3,1), MID(C2,2,1), MID(C2,1,1))


    Thanks for the other ideas.

    Regards,

    Sean

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,

    have a look at the magnificent use of the "StrReverse" function posted by a gosh darned good looking fellow.

    http://www.mrexcel.com/board/viewtop...c=4572&forum=2

    or you could just look up "StrReverse" in VBA help, it's up to you. Y'know, whatever.

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 12:16, EconSean wrote:
    I think I came up with a method to do this in a cell...

    =CONCATENATE(MID(C2,6,1), MID(C2,5,1), MID(C2,4,1), MID(C2,3,1), MID(C2,2,1), MID(C2,1,1))


    Thanks for the other ideas.

    Regards,

    Sean
    That will work, but only if you know the length of the string to be reversed beforehand. Best to use the LEN function to determine how many characters you're dealing with, then set up a loop to construct a new string using the MID function that loops thru the LENgth backwards (step -1). An example was posted above... but there's always more than one way to relieve a feline of their integuement...

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 12:21, Mark O'Brien wrote:
    Jay,

    have a look at the magnificent use of the "StrReverse" function posted by a gosh darned good looking fellow.

    http://www.mrexcel.com/board/viewtop...c=4572&forum=2

    or you could just look up "StrReverse" in VBA help, it's up to you. Y'know, whatever. [img]/board/images/smiles/icon_smile.gif[/img]
    Hi Mark,

    Is StrReverse a new feature? I don't have it on my system. Excel97, NT4.0. Anyway, I just saw your thread, and that would be much more efficient. Nice job.

    Also, you are very good with naming conventions for your variables. I have to get into that good habit.

    Bye,
    Jay


  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah, it's a new function from XL2000 onwards.

    Nice bit of code from y'self though.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •