Extract first two words of a cell

[projectile]

Hi

I need to extract the first two words from a cell. e.g.

A1---------------A2 (extract)

Mr Bill Gates------Mr Bill

I have tried =IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1)) , however this only extracts first word.


The words are separated by spaces " "


FYG, Once I have this cracked then I can compare data from two names databases using =ISNUMBER(MATCH(A2,ARRAYCOLUMN,0))


Thanks

 

[texasalynn]

try this

Excel Workbook
	C	D
77	Mr Bill Gates	Mr Bill
Stats

 

[texasalynn]

oops - forgot to remove 1 for the space

=LEFT(C77,(FIND("|",SUBSTITUTE(C77," ","|",2)))-1)

 

[projectile]

Hi Thanks for you quick response.

I didn't explain that some field may contain just 2 words. It works for cells that contain 3+ words, but not for cells that contain less. Is there anything that can be done about that?

 

[Richard Schollar]

Hi

Try:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),200))

 

[projectile]

Thanks, that works great.