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

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

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

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

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

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

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

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

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

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

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