Reverse Name with upper case with comma

[shrinivasmj]

hi,

I need to Reverse Name with some condition ,

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

Eg shown below

INPUT NAME OUTPUT NAME

N. Yulia Worbe	WORBE,N. YULIA	( Need to take space after Dot N. )
Gerardin N. T.	T,GERARDIN N.
N. T. Andreas	ANDREAS,N. T.
n.t. Romain	ROMAIN,N.T.	( No Space After Dot N.T. )
Marie Chupin P.L.N. P.	L.N. P,MARIE CHUPIN P.
P. L. O. P. Leon Tremblay	P. LEON TREMBLAY,P. L. O.
Marie Vidailhet N.	N,MARIE VIDAILHET
Olivier Colliot K. T. P.	T. P,OLIVIER COLLIOT K.
S.S. Arba	ARBA,S.S.

PLEASE FIND THE OLD FORMULA USED,BUT TAKING DOT IN REVERSE AND GIVING SPACE AFTER DOT.

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(N68)," ",REPT(" ",99)),(1+(LEN(N68)-LEN(SUBSTITUTE(N68," ",""))>2)+(LEN(N68)-LEN(SUBSTITUTE(N68," ",""))>4))*99))&","&N68,LEN(N68)))

[eisayev]

add substitute formula to this formula
=SUBSTITUTE(UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(C3)," ",REPT(" ",99)),(1+(LEN(N68)-LEN(SUBSTITUTE(C3," ",""))>2)+(LEN(C3)-LEN(SUBSTITUTE(C3," ",""))>4))*99))&","&C3,LEN(C3)))," ","")
this formula will delete the space bar

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

EG,

INPUT OUTPUT

N. Yulia Worbe

WORBE,N. YULIA

( NED TO TAKE AS INPUT SPACE AFTER DOT )

n.t. Romain

ROMAIN,N.T.

( No Space After Dot IN INPUT )

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

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