Retrieve first 3 words in a cell

  • Thread starter Thread starter KrispyData
  • Start date Start date
K

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!
 
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)
 
in
B1 1245 main street suite 10
in
A1 =LEFT(B1,FIND(" ",B1,1+FIND(" ",B1,1+FIND(" ",B1,1))))
 
There are at least 3 words in each cell?

Luke M said:
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!*
Click to expand...
 
Try the below formula

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

If this post helps click Yes
 
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!
Click to expand...


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