Retrieve first 3 words in a cell

[KrispyData]

I have an address field and I would like to create a formula to retrieve the
first 3 words in the address. How can I do this?

example:

1245 main street suite 10

i would like the result to be:

1245 main street

thanks so much for any help!

 

[Luke M]

This formula should work, provided there is always at least four words in the
cell:

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

 

[Bob I]

in
B1 1245 main street suite 10
in
A1 =LEFT(B1,FIND(" ",B1,1+FIND(" ",B1,1+FIND(" ",B1,1))))

 

[KrispyData]

There are at least 3 words in each cell?

This formula should work, provided there is always at least four words in the
cell:

=LEFT(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)-1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Jacob Skaria]

Try the below formula

=LEFT(SUBSTITUTE(A1 & " "," ",REPT(" ",255),3),255)

If this post helps click Yes

 

[KrispyData]

Perfect!
Thank you, Jacob!

Try the below formula

=LEFT(SUBSTITUTE(A1 & " "," ",REPT(" ",255),3),255)

If this post helps click Yes

 

[Ron Rosenfeld]

I have an address field and I would like to create a formula to retrieve the
first 3 words in the address. How can I do this?

example:

1245 main street suite 10

i would like the result to be:

1245 main street

thanks so much for any help!

=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)&" "," ",CHAR(1),3))-1)
--ron