CODEWITHSUNDEEP
excel
Tanzil
Tanzil

Reputation: 111

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

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.

Upvotes: 5

Views: 46623

Answers (6)

Nabeel Undre
Nabeel Undre

Reputation: 1

In A2 you should do the following:

=IF(A1="","",IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1),A1))

=TRIM(LEFT(A1, FIND("~",SUBSTITUTE(A1, " ", "~",2)&"~")))

Upvotes: 0

erdeepak
erdeepak

Reputation: 415

Use the code below to get the desired result:

=TRIM(LEFT(D1, FIND("~",SUBSTITUTE(D1, " ", "~",2)&"~")))

Upvotes: 0

HenryDC
HenryDC

Reputation: 1

In case there is a one word on your list.

=IFERROR(LEFT(H4,FIND(" ",H4,FIND(" ",H4)+1)-1),H4)

Upvotes: 0

MS Sankararaman
MS Sankararaman

Reputation: 362

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.

Upvotes: 7

Fls'Zen
Fls'Zen

Reputation: 4664

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)

Upvotes: 9

Dan
Dan

Reputation: 45762

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

Use the option start position parameter to only start looking after the first space which you find using find just like you've already done

Upvotes: 2

Related Questions