return first TWO or THREE words in string

E

EngelseBoer

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
 
L

Lars-Åke Aspelin

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
Click to expand...


Please explain the "or possibly 3" part of your problem better.

Lars-Åke
 
R

Ron Rosenfeld

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
Click to expand...

What determines if you want to return two vs three words?
--ron
 
E

EngelseBoer

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
 
E

EngelseBoer

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
 
R

Rick Rothstein

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.
 
E

EngelseBoer

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
 
E

EngelseBoer

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
 
Top