Reverse Name with upper case with comma

shrinivasmj

New member
Joined
Sep 18, 2012
Messages
3
Reaction score
0
Points
0
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)))
 
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
 
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 WorbeWORBE,N. YULIA( NED TO TAKE AS INPUT SPACE AFTER DOT )

n.t. RomainROMAIN,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,".",". ")))))
 
Back
Top