| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return first TWO or THREE words in string
similar to...
=LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
=LEFT(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-1) for the first two words
-- Gary''s Student - gsnu200803 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
On Sun, 7 Sep 2008 04:06:01 -0700, EngelseBoer
wrote: similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI Please explain the "or possibly 3" part of your problem better. Lars-Åke |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
On Sun, 7 Sep 2008 04:06:01 -0700, EngelseBoer
wrote: similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI What determines if you want to return two vs three words? --ron |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
nothing
where i need it i will need to alter the scriping for only those applicable i will use Gary's Student's reply thing is i am dealing with near 37,000 entries (dogs) hundreds of breeders so am running scripts for a variety of reason to colate the data into excel - then tab delim it and then upload to an online database in his instance - it is collecting the Breeders kennel Name form the dogs name "Ron Rosenfeld" wrote: On Sun, 7 Sep 2008 04:06:01 -0700, EngelseBoer wrote: similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI What determines if you want to return two vs three words? --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
thanks Gary''s Student
"Gary''s Student" wrote: =LEFT(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-1) for the first two words -- Gary''s Student - gsnu200803 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
kennel name could be something like
Molosser De Boeren 3 words in the title most are only 1 word titles a lot 2 and the occasion 3 but for these i can just copy and past the words really "Lars-Ã…ke Aspelin" wrote: On Sun, 7 Sep 2008 04:06:01 -0700, EngelseBoer wrote: similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI Please explain the "or possibly 3" part of your problem better. Lars-Ã…ke |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
You were asked a couple of times about the "or possibly 3" part of your
question and you said you would work around it. That might not be necessary assuming you asked the "wrong" question. Is your actual question "How do I return all but the last word in an entry?" That is, is the text following the part you want **always** a single word (containing no internal spaces)? If so, you can use this formula to return the words in front of it... =SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"") If your newsreader breaks the above formula into two lines, the break will have occurred at a blank space in the formula, so make sure you include it when recombining the line. -- Rick (MVP - Excel) "EngelseBoer" wrote in message ... similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
how about this one ??
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)) On Sep 7, 4:06*pm, EngelseBoer wrote: similar to... =LEFT(A1,SEARCH(" ",A1)-1) * * * * but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A * * * * * * * * * * * * * * * * * * * * *B 100 AKER BALTO * * * * * * * * * 100 AKER 100 AKER BASTION * * * * * * * *100 AKER 100 AKER BODECIA * * * * * * * *100 AKER DE WET SISQO * * * * * * * * * * *DE WET DE WET SKYE * * * * * * * * * * * *DE WET DE WET STOFFEL * * * * * * * * *DE WET EL SHADAI LULU * * * * * * * * * *EL SHADAI EL SHADAI MARMITE * * * * * * EL SHADAI EL SHADAI MIMI * * * * * * * * * *EL SHADAI |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
no...
and i did reply (and thought of that - return all but last) so for the few kennel name with 2 or 3 words seems best if i there edit it and merely copy the correct and drag down to where i need to they are in Alphbet order compare..eg (and anything less or more) Kennel name Molosser De Boeren animal Name Queen of Sheba v/s Kennel name Aricon animal Name Sweet Pea "Rick Rothstein" wrote: You were asked a couple of times about the "or possibly 3" part of your question and you said you would work around it. That might not be necessary assuming you asked the "wrong" question. Is your actual question "How do I return all but the last word in an entry?" That is, is the text following the part you want **always** a single word (containing no internal spaces)? If so, you can use this formula to return the words in front of it... =SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"") If your newsreader breaks the above formula into two lines, the break will have occurred at a blank space in the formula, so make sure you include it when recombining the line. -- Rick (MVP - Excel) "EngelseBoer" wrote in message ... similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return first TWO or THREE words in string
maybe i need put it better
compare..eg (and anything less or more) Kennel name Molosser De Boeren animal Name Queen of Sheba thus name = Molosser De Boeren Queen of Sheba v/s Kennel name Aricon animal Name Sweet Pea thus name = Aricon Sweet Pea "Rick Rothstein" wrote: You were asked a couple of times about the "or possibly 3" part of your question and you said you would work around it. That might not be necessary assuming you asked the "wrong" question. Is your actual question "How do I return all but the last word in an entry?" That is, is the text following the part you want **always** a single word (containing no internal spaces)? If so, you can use this formula to return the words in front of it... =SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"") If your newsreader breaks the above formula into two lines, the break will have occurred at a blank space in the formula, so make sure you include it when recombining the line. -- Rick (MVP - Excel) "EngelseBoer" wrote in message ... similar to... =LEFT(A1,SEARCH(" ",A1)-1) but where the 1st 2 (or possibly 3) words are required to be returned what do i need to change or what script do i need here A B 100 AKER BALTO 100 AKER 100 AKER BASTION 100 AKER 100 AKER BODECIA 100 AKER DE WET SISQO DE WET DE WET SKYE DE WET DE WET STOFFEL DE WET EL SHADAI LULU EL SHADAI EL SHADAI MARMITE EL SHADAI EL SHADAI MIMI EL SHADAI |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find a string of nth occurance & pick next 3 words | Excel Worksheet Functions | |||
| split string based on a SET of words | Excel Discussion (Misc queries) | |||
| i need help creating a function that will extract words from a string of text | Excel Worksheet Functions | |||
| Extracting the last set of words from a text string | Excel Worksheet Functions | |||
| How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
Linear Mode
