Reverse Text with Criteria not All text can be reversed

[muhammad susanto]

hi all...

i have problem how to reverse order text with criteria like this :
Excel 2013 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]PT THE BIS BOSS[/TD]
[TD]THE BIS BOSS, PT[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]CV. MAKE YOU HAPPY[/TD]
[TD]MAKE YOU HAPPY, CV[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]H. MICHAEL NOORMAN[/TD]
[TD]MICHAEL NOORMAN, H[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]THE LION KING[/TD]
[TD]THE LION KING[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]MICHAEL JORDAN FRANKY LARGE[/TD]
[TD]MICHAEL JORDAN FRANKY LARGE[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]ETC..[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Aladin Akyurek]

Advise: Try always to specify the desired output (instead of discussing a non-rorking formula).

 

[muhammad susanto]

maybe like this more clear :

Excel 2013 64 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td=bgcolor:#FFFF00]NAME/INPUT[/td][td=bgcolor:#FFFF00]EXPECTED RESULT[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]PT THE BIS BOSS[/td][td]THE BIS BOSS, PT[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]CV. MAKE YOU HAPPY[/td][td]MAKE YOU HAPPY, CV[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]H. MICHAEL NOORMAN[/td][td]MICHAEL NOORMAN, H[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]THE LION KING[/td][td]THE LION KING[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]MICHAEL JORDAN FRANKY LARGE[/td][td]MICHAEL JORDAN FRANKY LARGE[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]ETC..[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Peter_SSs]

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
	F	G
3	NAME/INPUT	EXPECTED RESULT
4	PT THE BIS BOSS	THE BIS BOSS, PT
5	CV. MAKE YOU HAPPY	MAKE YOU HAPPY, CV
6	H. MICHAEL NOORMAN	MICHAEL NOORMAN, H
7	THE LION KING	THE LION KING
8	MICHAEL JORDAN FRANKY LARGE	MICHAEL JORDAN FRANKY LARGE
Reorder text

 

[muhammad susanto]

Peter, wow,...working great!! thanks you so much....

 

[Peter_SSs]

Peter, wow,...working great!! thanks you so much....

Glad to help. Thanks for letting us know.

 

[muhammad susanto]

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

 

[Peter_SSs]

I'm not certain what you are saying. Are you saying that both of those should stay exactly as they were?

 

[muhammad susanto]

yes...you're right..
they should be exactly not change

 

[Peter_SSs]

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),".",""))