Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
superscript in part of a string when using concatenate | Excel Discussion (Misc queries) | |||
Extract Part of String | Excel Worksheet Functions | |||
select part of a cell value string | Excel Discussion (Misc queries) | |||
Summing part of an Alpha Numeric String | Excel Worksheet Functions | |||
Part Number/Qty Consolidations | Excel Discussion (Misc queries) |