How to extract first 3 words from another cell

I would like to extract the first three words from another cell.
However in case the cell includes less than 3 words, it would keep the original amount of words.

Example:

from

A1: Word to fit in this
A2: John Doe
A3: Once upon a time

to

A1: Word to fit
A2: John Doe
A3: Once upon a

I've only found the formula for extracting first two words, but not the third:
=LEFT(A1, FIND(" ", A1, FIND(" ", A1) + 1) - 1)

Originally Posted by papusale

Thank you.

You're welcome

Originally Posted by papusale

Thank you. Although the formula worked, whenever there's less than 3 words, it gives me #VALUE! error.
Any way to bypass this?

Did you see my comments?

Originally Posted by Jonmo1

Note, the part I highlighted red needs to contain 3 spaces between the quote marks (forum software trims consecutive spaces)

Originally Posted by Jonmo1

Try

=LEFT(A1,FIND("^",SUBSTITUTE(A1&" "," ","^",3))-1)

Note, the part I highlighted red needs to contain 3 spaces between the quote marks (forum software trims consecutive spaces)
And the two ^ can be any random character of your choice that is least likely to actually appear in the original strings.

Actually I now realized that even though it will keep a two word cell in tact, it will give #VALUE! if the cell only contains one word.
Any way to bypass this?

Works just fine for me.

EFpapusale.xlsx

Originally Posted by Jonmo1

Works just fine for me.

Attachment 414688

Yes. I copied your formula to my workbook and it worked. Very sorry for the confusion!

No confusion here.

Glad you got it working.

Originally Posted by Jonmo1

No confusion here.

Glad you got it working.

Hi Jonmo1,

I wanted to ask that if I want to substitute the empty space with for example a slash "/", which parts of the formula do I have to replace?

For example if words are separated like this:
Sports/activities/football
Sports/tennis
etc.

Do you mean that the original string is now seperated by slashes instead of spaces?
Then just replace all spaces in the formula with slashes (remember the part that is 3 spaces would be 3 slashes)

Or you want your result to now have / instead of space?
You can wrap the whole formula in a substitute

=SUBSTITUTE(LEFT(A1,FIND("^",SUBSTITUTE(A1&" "," ","^",3))-1)," ","/")

Originally Posted by Jonmo1

Do you mean that the original string is now seperated by slashes instead of spaces?
Then just replace all spaces in the formula with slashes (remember the part that is 3 spaces would be 3 slashes)

Or you want your result to now have / instead of space?
You can wrap the whole formula in a substitute

=SUBSTITUTE(LEFT(A1,FIND("^",SUBSTITUTE(A1&" "," ","^",3))-1)," ","/")

Hi Jonmo1,

Yes, the original string is separated by slashes.

I guess it's this then:
=LEFT(A1,FIND("^",SUBSTITUTE(A1&"///","/","^",3))-1)

Correct?

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

paste this any cell and copy down.

Originally Posted by papusale

Hi Jonmo1,

Yes, the original string is separated by slashes.

I guess it's this then:
=LEFT(A1,FIND("^",SUBSTITUTE(A1&"///","/","^",3))-1)

Correct?

That's correct