Filter on first part of string
 

How should i go about filtering a column to return only those cells where
there is a single word contained in them and leave out those cell where
there is a string of text.

Example

Abelia
Abelia chinensis
Abies
Abies alba
Abromeitiella
Abromeitiella brevifolia
Abutilon
Abutilon 'Anneke'


after filtering returns
Abelia
Abies
Abromeitiella
Abutilon

Thanks if you can help.
Pat

DataFilterAutofilter...
Customdoes not contain(space - hit space bar once)

"Pat" wrote:

How should i go about filtering a column to return only those cells where
there is a single word contained in them and leave out those cell where
there is a string of text.

Example

Abelia
Abelia chinensis
Abies
Abies alba
Abromeitiella
Abromeitiella brevifolia
Abutilon
Abutilon 'Anneke'


after filtering returns
Abelia
Abies
Abromeitiella
Abutilon

Thanks if you can help.
Pat

Pat,

I don't think the filter can be set up to filter what you need directly, but
it could be done via a helper column:

=ISNUMBER(SEARCH(" ",A2,1))
Filter for FALSE

=NOT(ISNUMBER(SEARCH(" ",A2,1)))
Filter for TRUE

Or, for a better presentation:
=IF(NOT(ISNUMBER(SEARCH(" ",A1,1))),"yes", "no")
Here you can put in your own values to appear in the helper column. Change
the "yes" and "no" to suit.

These will return incorrect results if there are spaces at the beginning or
ending of the values. If that's a possibility, those should be removed, or
the formulas here changed:
=IF(NOT(ISNUMBER(SEARCH(" ",TRIM(A2),1))),"yes", "no")
--
Earl Kiosterud
www.smokeylake.com

"Pat" wrote in message
...
How should i go about filtering a column to return only those cells where
there is a single word contained in them and leave out those cell where
there is a string of text.

Example

Abelia
Abelia chinensis
Abies
Abies alba
Abromeitiella
Abromeitiella brevifolia
Abutilon
Abutilon 'Anneke'


after filtering returns
Abelia
Abies
Abromeitiella
Abutilon

Thanks if you can help.
Pat

Pat,
Say the column you want filtered that way is column A and assuming
there are no leading spaces, you could put =FIND(" ",A1) into the top
of any other free column then fill down as far as column A. You could
then filter this column, choosing #VALUE! because that is the FIND
function's result when, in this case, a space is not found, indicating
one word.
Does that help?
Ken Johnson

Thanks again David, that worked fine.

Is there a way to filter a list where repeated words are filtered out to
reveal only one instance of each word.


"David Hepner" wrote in message
...
DataFilterAutofilter...
Customdoes not contain(space - hit space bar once)

"Pat" wrote:

How should i go about filtering a column to return only those cells
where
there is a single word contained in them and leave out those cell where
there is a string of text.

Example

Abelia
Abelia chinensis
Abies
Abies alba
Abromeitiella
Abromeitiella brevifolia
Abutilon
Abutilon 'Anneke'


after filtering returns
Abelia
Abies
Abromeitiella
Abutilon

Thanks if you can help.
Pat

Hi gentlemen,

I am afraid none of your suggestions worked.
Let me give another example;

colA

Acalypha
Acanthocalycium
Acantholimon
Acanthopanax
Acanthus
Acca
Acer
Acer
Acer
Acer
Acer
Aceras
Aceriphyllum
Achillea
Achillea
Achimenes

result after filtering;

Acalypha
Acanthocalycium
Acantholimon
Acanthopanax
Acanthus
Acca
Acer
Aceras
Aceriphyllum
Achillea
Achimenes


regards
Pat


"Ken Johnson" wrote in message
oups.com...
Pat,
Say the column you want filtered that way is column A and assuming
there are no leading spaces, you could put =FIND(" ",A1) into the top
of any other free column then fill down as far as column A. You could
then filter this column, choosing #VALUE! because that is the FIND
function's result when, in this case, a space is not found, indicating
one word.
Does that help?
Ken Johnson

I just realised the suggestions you gentlemen provided are indeed correct,
apologises for mixing up a new question with the answer you both gave.
I will put the new question forward separately

Pat

"Pat" wrote in message
...
Hi gentlemen,

I am afraid none of your suggestions worked.
Let me give another example;

colA

Acalypha
Acanthocalycium
Acantholimon
Acanthopanax
Acanthus
Acca
Acer
Acer
Acer
Acer
Acer
Aceras
Aceriphyllum
Achillea
Achillea
Achimenes

result after filtering;

Acalypha
Acanthocalycium
Acantholimon
Acanthopanax
Acanthus
Acca
Acer
Aceras
Aceriphyllum
Achillea
Achimenes


regards
Pat


"Ken Johnson" wrote in message
oups.com...
Pat,
Say the column you want filtered that way is column A and assuming
there are no leading spaces, you could put =FIND(" ",A1) into the top
of any other free column then fill down as far as column A. You could
then filter this column, choosing #VALUE! because that is the FIND
function's result when, in this case, a space is not found, indicating
one word.
Does that help?
Ken Johnson