|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 27th, 2009, 04:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve first 3 words in a cell
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! 
|
|
#2
October 27th, 2009, 04:55 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a 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!* "KrispyData" wrote: 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!
|
|
#3
October 27th, 2009, 05:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a cell
in
B1 1245 main street suite 10 in A1 =LEFT(B1,FIND(" ",B1,1+FIND(" ",B1,1+FIND(" ",B1,1)))) KrispyData wrote: 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!
|
|
#4
October 27th, 2009, 05:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a cell
There are at least 3 words in each cell?
"Luke M" wrote: 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!* "KrispyData" wrote: 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!
|
|
#5
October 27th, 2009, 05:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a cell
Try the below formula
=LEFT(SUBSTITUTE(A1 & " "," ",REPT(" ",255),3),255) If this post helps click Yes --------------- Jacob Skaria "KrispyData" wrote: There are at least 3 words in each cell? "Luke M" wrote: 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!* "KrispyData" wrote: 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!
|
|
#6
October 27th, 2009, 05:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a cell
Perfect!
Thank you, Jacob! "Jacob Skaria" wrote: Try the below formula =LEFT(SUBSTITUTE(A1 & " "," ",REPT(" ",255),3),255) If this post helps click Yes --------------- Jacob Skaria "KrispyData" wrote: There are at least 3 words in each cell? "Luke M" wrote: 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!* "KrispyData" wrote: 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!
|
|
#7
October 27th, 2009, 06:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retrieve first 3 words in a cell
On Tue, 27 Oct 2009 09:46:01 -0700, KrispyData
wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
