What is the formula to keep first two words in a cell over excel?

Question

My D1 cell contains name like John Smith Rangpur Bangladesh 5400... i want to keep first two words like John Smith and delete rest from cell. What will be formula to do that?

I've used this formula on C1 =LEFT(D1,FIND(" ",D1)-1) but it returnes 1st word (John) only. How to get 1st and 2nd word like John Smith ? My word are separated with space.

Thanks in advance.

Answer 1

You need to use the third parameter of the FIND function. It's the position where the find operation starts. If you give it the character after the first space, then you'll find the second space, which is what you need.

=LEFT(D1, FIND(" ", D1, FIND(" ", D1) + 1) - 1)

Answer 2

This is a more scalable formula:

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

The string/ character separator is " " (Space), whose instance number decides it all.

The Substitute function allows the user to replace a string for the given instance number, which makes it simple to have different instance numbers (First two words of the phrase, first five words of the phrase and so on) for each of the cases. We replace the separator of the given instance (Say 2nd in this case) with a new separator "|" to find it's position from the left to determine the required output.

In the above answers which have multiple find functions within the same function, if the user wants the first 5 words instead of 2, the function becomes heavily complex with the use of multiple find functions within the same function.

In this case, the user simply needs to substitute the '2' in the substitute function (which represents the instance number) to 5.