change the formula as it give all space after dot .

[shrinivasmj]

if i run this formula i need the output with out space after dot , if input is without space,
if there is space after dot in input ,then out put is with space after dot.

need to change the formula as it give all 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,".",". ")))))

*.input srinivas , out put is blank . its not taking single word.

*.input srinivas m.j.k ,out put J. K,SRINIVAS M. , if the input is without space after dot. then the out should come with out space as --J.K,SRINIVAS M.

 

[BiocideJ]

The obvious answer would be to change all of the instances of ". " to "." {without the space}

This makes the majority of the calculations in the formula unnecessary, however.
I offer this simplified version which I believe does what you need, but without knowing the original criteria this formula was meeting I cannot guarantee it.

=UPPER(MID(A1&","&A1,FIND("$",SUBSTITUTE(A1," ","$",LOOKUP(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1,{0,3,5},(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))-{0,1,2})))+1,LEN(A1)))

 

[shrinivasmj]

hi,input - srinivas m.j.k out put J. K,SRINIVAS M. , with the old formula .=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 ONE FORMULA , NEED TO MODIFY THE FORMULA