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
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
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 1PETS 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
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.
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
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)
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
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
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)))
Here is a solution for 10 or less words
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).
Wow, thank you for this. This is very simple and easy to modify.
Thank you and God bless you.
Thank you so much. It works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks