+ Reply to Thread
Results 1 to 12 of 12

How to invert text in a cell

  1. #1
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    How to invert text in a cell

    Hi everyone,
    I want to invert the words in a cell for example
    CELL A1 CONTAINS : "PETS ARE FRIENDS"
    IN CELL B1 it will convert to : "FRIENDS ARE PETS"

    Thank you

    Rolly

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: How to invert text in a cell

    Maybe this

    =TRIM(RIGHT(A1,FIND("^",SUBSTITUTE(A1," ","^",2))-1)&MID(A1,FIND("^",SUBSTITUTE(A1," ","^",1)),LEN((LEN(SUBSTITUTE(A1," ","^",2)))*LEN(SUBSTITUTE(A1," ","^",1)))+1)&" "&LEFT(A1,FIND(" ",A1)-1))



    A
    B
    1
    PETS ARE FRIENDS FRIENDS ARE PETS
    Last edited by AlKey; 03-16-2014 at 09:12 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to invert text in a cell

    Hi Alkey,
    Thank you so much for your reply. This is my first time to post and so thankful I got an answer instantly.
    It worked on "PETS ARE FRIENDS"
    But when I tried "GOD IS GOOD" it gave me "S GOOD IS GOD"
    It has extra S in front.
    when I try "TIME IS GOLD"
    IT GIVES ME "IS GOLD IS TIME"


    Thank you so much
    Last edited by rollymilitar; 03-16-2014 at 09:35 PM.

  4. #4
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to invert text in a cell

    Hi Alkey,
    Thank you so much for your reply. This is my first time to post and so thankful I got an answer instantly.
    It worked on "PETS ARE FRIENDS"
    But when I tried "GOD IS GOOD" it gave me "S GOOD IS GOD"
    It has extra S in front.
    When I tried "TIME IS GOLD"
    it gives me "IS GOLD IS TIME"


    Thank you so much

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: How to invert text in a cell

    Sorry about that here is adjusted formula. It only work for three words.

    =REPLACE(A1,1,FIND("^",SUBSTITUTE(A1," ","^",2)),"")&MID(A1,FIND("^",SUBSTITUTE(A1," ","^",1)),LEN((LEN(SUBSTITUTE(A1," ","^",2)))*LEN(SUBSTITUTE(A1," ","^",1)))+1)&" "&LEFT(A1,FIND(" ",A1)-1)

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,991

    Re: How to invert text in a cell

    If you only have 3 words, try this....
    =MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,99)&" "&MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,FIND(" ",A1,1)+1))-FIND(" ",A1,1)-1)&" "&LEFT(A1,FIND(" ",A1,1)-1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to invert text in a cell

    WOW IT WORKS.
    I WILL TRY MODIFYING IT TO WORK ON MORE THAN FOUR WORDS.

    Thank you so much.

    Your help made someone happy today

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,991

    Re: How to invert text in a cell

    If you are looking for more than 3 words, this will get very cumbsy and bulky

    2 other suggestions...
    use text to columns and then combine back as needed
    use this, copied across, and then combine back as needed...
    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*(COLUMN()-2)+1,LEN($A1)))

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: How to invert text in a cell

    Here is a solution for 10 or less words
    Attached Files Attached Files

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: How to invert text in a cell

    You're welcome and thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  11. #11
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to invert text in a cell

    Wow, thank you for this. This is very simple and easy to modify.

    Thank you and God bless you.

  12. #12
    Registered User
    Join Date
    03-16-2014
    Location
    AUSTRALIA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to invert text in a cell

    Thank you so much. It works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Is there a way to invert a column?
    By ukdodger in forum Excel General
    Replies: 5
    Last Post: 12-22-2012, 03:26 PM
  2. Invert default cell colour
    By pkrmark in forum Excel General
    Replies: 1
    Last Post: 10-15-2012, 01:12 PM
  3. Invert Chart
    By tek9step in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-09-2009, 08:22 AM
  4. [SOLVED] How do I invert text (abc-->cba)
    By Moset in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2006, 02:35 PM
  5. [SOLVED] Invert Text
    By Gary's Student in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 10:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1