Names Split

[bharath]

Hi,
Here i am attaching a file
Requirement is
Mohan Das Karam Chand Gandhi Gujrath
i want only MOhan Das Karam Chand Gandhi
and one more Mohan Das K Delhi
i Want on Mohan Das K

Thank You.

 

[bharath]

sorry
in column f i want result like as shown

 

[Debraj]

Hi Bharath,

Try this.. in F2

=IFERROR(LEFT(E2,FIND("|",SUBSTITUTE(E2," ","|",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))),E2)

 

[Hui]

Bharath

try:
=LEFT(E2,IF(LEN(E2)-LEN(SUBSTITUTE(E2," ",""))<=1,LEN(E2),FIND(CHAR(135),SUBSTITUTE(E2," ",CHAR(135),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))-1))

With inspiration from: http://chandoo.org/wp/2012/05/17/formula-forensic-no-021/

 

[kchiba]

Hi try this:

=LEFT(E2,FIND("~",SUBSTITUTE(E2," ","~",LEN(E2)-LEN(SUBSTITUTE(E2," ","")))))

 

[bharath]

Hi Bharath,

Try this.. in F2

=IFERROR(LEFT(E2,FIND("|",SUBSTITUTE(E2," ","|",LEN(E2)-LEN(SUBSTITUTE(E2," ",""))))),E2)

thanks a lot

 

[bharath]

thank you all for helping me
thank you so much..