Splitting name with various combinations using excel formulas

[RAJERAM1]

Hi,


I have a name in cell a1 (arun kumar gupta). I need out put in cell b1 as (arun k gupta,arun gupta,gupta arun,arun kumar,arun kumar g,g arun kumar,a k gupta).


Kindly show me with formula how to split "arun kumar gupta" with various name combinations as i mentioned above into single cell followed by comma(,).


Can anyone help me.....


I looking forward your response.

[sambit]

Hi,
Try in cell B1

=LEFT(A1,FIND(" ",A1,1)-1)&" "&MID(A1,FIND(" ",A1,1)+1,1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,99))&","&LEFT(A1,FIND(" ",A1,1)-1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,99))&","&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,99))&" "&LEFT(A1,FIND(" ",A1,1)-1)&","&LEFT(A1,FIND(" ",A1,1)-1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99)&","&LEFT(A1,FIND(" ",A1,1)-1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,11))&","&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,11))&" "&LEFT(A1,FIND(" ",A1,1)-1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99))&","&LEFT(A1,1)&" "&MID(A1,FIND(" ",A1,1)+1,1)&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),198,99)))

[RAJERAM1]

This is what I expect.

Amazing.....Thank you so much.

regards,
Ramesh R