shrinivasmj
New member
- Joined
- Sep 18, 2012
- Messages
- 3
- Reaction score
- 0
- Points
- 0
hi,
input - srinivas m.j.k
out put J. K,SRINIVAS M. , with the below formula need to remove space after dot if input is without space after dot
=UPPER(MID(TRIM(SUBSTITUTE(A1,".",". "))&","&TRIM(SUBSTITUTE(A1,".",". ")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(A1,".",". "))," ","$",LOOKUP(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(SUBSTITUTE(A1,".",". ")," ","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(A1,".",". ")))))
1. Input - srinivas m. j. k.
output is J. K,SRINIVAS M.
OR
2. input - srinivas m.j.k ,
output - J.K,SRINIVAS M.
I NEED THE 1 AND 2 OUT PUT WITH ABOVE FORMULA , NEED TO MODIFY THE FORMULA
used condiction to above formula
ALL UPPER CASE
1 to 3 words with fullstop reverse 1 last word
3 to 5 words with fullstop reverse 2 last word
6 words above with fullstop reverse 3 last word
need to take m.k.l. as 3 words
input - srinivas m.j.k
out put J. K,SRINIVAS M. , with the below formula need to remove space after dot if input is without space after dot
=UPPER(MID(TRIM(SUBSTITUTE(A1,".",". "))&","&TRIM(SUBSTITUTE(A1,".",". ")),FIND("$",SUBSTITUTE(TRIM(SUBSTITUTE(A1,".",". "))," ","$",LOOKUP(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1,{0,4,6},(LEN(TRIM(SUBSTITUTE(A1,".",". ")))-LEN(SUBSTITUTE(SUBSTITUTE(A1,".",". ")," ","")))-{0,1,2})))+1,LEN(TRIM(SUBSTITUTE(A1,".",". ")))))
1. Input - srinivas m. j. k.
output is J. K,SRINIVAS M.
OR
2. input - srinivas m.j.k ,
output - J.K,SRINIVAS M.
I NEED THE 1 AND 2 OUT PUT WITH ABOVE FORMULA , NEED TO MODIFY THE FORMULA
used condiction to above formula
ALL UPPER CASE
1 to 3 words with fullstop reverse 1 last word
3 to 5 words with fullstop reverse 2 last word
6 words above with fullstop reverse 3 last word
need to take m.k.l. as 3 words