# How to invert text in a cell

1. ## 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. ## 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

3. ## 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

4. ## 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. ## 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. ## 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)

7. ## 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.

8. ## 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. ## Re: How to invert text in a cell

Here is a solution for 10 or less words

10. ## Re: How to invert text in a cell

You're welcome and thank you for the feedback!

11. ## 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. ## Re: How to invert text in a cell

Thank you so much. It works.

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

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