Reverse Name with upper case and break with comma

[shrinivasmj]

hi currently using this formula ,



i need , if the name as space after dot . output need to take space
after dot or if no space after dot need to take with out space after
dot in output.

NEED TO GET same output for one formula NEED TO EDIT .




EG,



INPUT OUTPUT

[TABLE="class: cms_table_cms_table, width: 595"]
<tbody>[TR]
[TD]N. Yulia Worbe[/TD]

[TD]WORBE,N. YULIA[/TD]

[TD]( NED TO TAKE AS INPUT SPACE AFTER DOT )[/TD]

[/TR]

</tbody>[/TABLE]




[TABLE="class: cms_table_cms_table, width: 595"]
<tbody>[TR]
[TD]n.t. Romain[/TD]

[TD]ROMAIN,N.T.[/TD]

[TD]( No Space After Dot IN INPUT )[/TD]

[/TR]

</tbody>[/TABLE]




formula..........used

=UPPER(MID(TRIM(SUBSTITUTE(H14,".",".
"))&","&TRIM(SUBSTITUTE(H14,".",".
")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(H14,".",". ")),"
","$",LOOKUP(LEN(TRIM(SUBSTITUTE(H14,".",".
")))-LEN(SUBSTITUTE(TRIM(H14),"
",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(H14,".",".
")))-LEN(SUBSTITUTE(SUBSTITUTE(H14,".",". "),"
","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(H14,".",". ")))))

---

---

I need to Reverse Name with upper case and break with comma



need to break the name in half part and reverse the name with comma
,and need to take dot as one word like EG- shrinivas m.j. are 3 words .



1 to 3 words reverse last word





> 4 to 5 words reverse last 2 words



> 6 to 7 words reverse last 3 words

 

[Ron Coderre]

Try this regular formula...

=TRIM(SUBSTITUTE(UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(H14," ",REPT(" ",LEN(H14))),LEN(H14)
*CHOOSE(LEN(H14)-LEN(SUBSTITUTE(H14," ",""))+1,1,1,1,2,2,3,3,4,4,))&","&H14),LEN(H14))),".",". "))

Is that something you can work with?

 

[shrinivasmj]

its not working its taking space if

shrinivas m.j.k. input

M. J. K. ,SHRINIVAS out put

its still giving space after .

the out put should come
M.J,SHRINIVAS M.

NEED TO TAKE LAST TWO WORD

 

[Jborg]

try this maybe it will work.

=UPPER(RIGHT(E14,LEN(E14)-MATCH(2,1/--(MID(E14,ROW(INDIRECT("1:"&LEN(E14))),1)=" "))))&","&UPPER(MID(E14,1,MATCH(2,1/--(MID(E14,ROW(INDIRECT("1:"&LEN(E14))),1)=" "))))

 

[shrinivasmj]

its not working

 

[Jborg]

Change the Cell E14 with the cell where you have the Text, and it should work. It is working with mine.

 

[shrinivasmj]

[TABLE="width: 297"]
<tbody>[TR]
[TD]srinivas m.j.k.l[/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

its giving this output

 

[Jborg]

I have just copied your text and it is working with mine.

[TABLE="width: 64"]
<tbody>[TR]
[TD]srinivas m.j.k.l M.J.K.L,SRINIVAS [TABLE="width: 115"]
<tbody>[TR]
[TD]
isn't this what wanted?? [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 321"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col></colgroup>[/TABLE]

 

[Jborg]

=upper(right(c14,len(c14)-match(2,1/--(mid(c14,row(indirect("1:"&len(c14))),1)=" "))))&","&upper(mid(c14,1,match(2,1/--(mid(c14,row(indirect("1:"&len(c14))),1)=" "))))

 

[shrinivasmj]

I need to Reverse Name with upper case and break with comma



need to break the name in half part and reverse the name with comma
,and need to take dot as one word like EG- shrinivas m.j. are 3 words .



1 to 3 words reverse last word





> 4 to 5 words reverse last 2 words



> 6 to 7 words reverse last 3 words


current formula used

=UPPER(MID(TRIM(SUBSTITUTE(H14,".",".
"))&","&TRIM(SUBSTITUTE(H14,".",".
")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(H14,".",". ")),"
","$",LOOKUP(LEN(TRIM(SUBSTITUTE(H14,".",".
")))-LEN(SUBSTITUTE(TRIM(H14),"
",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(H14,".",".
")))-LEN(SUBSTITUTE(SUBSTITUTE(H14,".",". "),"
","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(H14,".",". ")))))