Reverse Text with Criteria not All text can be reversed

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all...

i have problem how to reverse order text with criteria like this :
Excel 2013 64 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
PT THE BIS BOSSTHE BIS BOSS, PT
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
CV. MAKE YOU HAPPYMAKE YOU HAPPY, CV
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
H. MICHAEL NOORMANMICHAEL NOORMAN, H
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
THE LION KINGTHE LION KING
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
MICHAEL JORDAN FRANKY LARGEMICHAEL JORDAN FRANKY LARGE
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
ETC..

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

i have use :
=MID(TRIM(A1);4;255)&", "&LEFT(TRIM(A1);2)
but not fully working for the lion king.....and michael jordan not work for both

how to make work well...

thanks/m.susanto
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
maybe like this more clear :

Excel 2013 64 bit
F
G
3
NAME/INPUTEXPECTED RESULT
4
PT THE BIS BOSSTHE BIS BOSS, PT
5
CV. MAKE YOU HAPPYMAKE YOU HAPPY, CV
6
H. MICHAEL NOORMANMICHAEL NOORMAN, H
7
THE LION KINGTHE LION KING
8
MICHAEL JORDAN FRANKY LARGEMICHAEL JORDAN FRANKY LARGE
9
ETC..
Sheet: Sheet1
 
Last edited:
Upvote 0
From those examples, this is the best I can guess. If that is not what you need, in words, can you give us some "rules" about when to move the first text to the end and when not to?

Excel Workbook
FG
3NAME/INPUTEXPECTED RESULT
4PT THE BIS BOSSTHE BIS BOSS, PT
5CV. MAKE YOU HAPPYMAKE YOU HAPPY, CV
6H. MICHAEL NOORMANMICHAEL NOORMAN, H
7THE LION KINGTHE LION KING
8MICHAEL JORDAN FRANKY LARGEMICHAEL JORDAN FRANKY LARGE
Reorder text
 
Upvote 0
hi peter..

once again using your formula for the text below can reverse too
m. jack pablo aimar
m alan tahli major

how to make for only text with "m......" can not be reversed? should be still:
m. jack pablo aimar
m alan tahli major
 
Upvote 0
I'm not certain what you are saying. Are you saying that both of those should stay exactly as they were?
 
Upvote 0
Try
=IF(OR(FIND(" ",SUBSTITUTE(F4,".","")&" ")>3,LEFT(F4,2)="m ",LEFT(F4,3)="m. "),F4,SUBSTITUTE(MID(F4&", " & F4,FIND(" ",F4)+1,LEN(F4)+1),".",""))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top