| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I sort a excel file by how many words ie all lines with 2 w
I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with 2 w
If I look at a typical row, are the keywords all in the same cell or does
each keyword have its own cell?? -- Gary''s Student - gsnu200812 "Chops" wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
The keywords are in the same cell, so if you look at the excel sheet it would
go something like this xxxxx xxxxx yyyyy xxxx xxxx yyyy wwww yyyy meaning some rows have one word some have 2 or 3 or even more. So basically I have rows with single keywords and then some with long tailed keywords. But I want to make a new sheet just showing the rows that have one or two keywords in them. I hope I am making sense Thanx Carolin "Gary''s Student" wrote: If I look at a typical row, are the keywords all in the same cell or does each keyword have its own cell?? -- Gary''s Student - gsnu200812 "Chops" wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with 2 w
If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way: Autofilter the list, then in the column C autofilter dropdown, choose "Show Blanks" If the words are in separate cells, and the columns may be randomly filled or blank, in E1 enter =COUNTA(A1:D1) and filter on column E <= 2 If the words are in one cell per row (e.g., in column A), separated by commas, put this in B1: =COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Filter column B on B <= 2 In article , Chops wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
they are in the sme row, but not seperated by commas there is just a space
between the words. Does the second formula still work. Thanx Carolin "JE McGimpsey" wrote: If the words are in separate cells, (e.g., columns A:D), and A is filled first, then B,C,D, then one way: Autofilter the list, then in the column C autofilter dropdown, choose "Show Blanks" If the words are in separate cells, and the columns may be randomly filled or blank, in E1 enter =COUNTA(A1:D1) and filter on column E <= 2 If the words are in one cell per row (e.g., in column A), separated by commas, put this in B1: =COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Filter column B on B <= 2 In article , Chops wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
No, but a modification of the third one will:
=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) In article , Chops wrote: they are in the sme row, but not seperated by commas there is just a space between the words. Does the second formula still work. Thanx Carolin "JE McGimpsey" wrote: If the words are in separate cells, (e.g., columns A:D), and A is filled first, then B,C,D, then one way: Autofilter the list, then in the column C autofilter dropdown, choose "Show Blanks" If the words are in separate cells, and the columns may be randomly filled or blank, in E1 enter =COUNTA(A1:D1) and filter on column E <= 2 If the words are in one cell per row (e.g., in column A), separated by commas, put this in B1: =COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Filter column B on B <= 2 In article , Chops wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this. Thanx Carolin "JE McGimpsey" wrote: No, but a modification of the third one will: =COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) In article , Chops wrote: they are in the sme row, but not seperated by commas there is just a space between the words. Does the second formula still work. Thanx Carolin "JE McGimpsey" wrote: If the words are in separate cells, (e.g., columns A:D), and A is filled first, then B,C,D, then one way: Autofilter the list, then in the column C autofilter dropdown, choose "Show Blanks" If the words are in separate cells, and the columns may be randomly filled or blank, in E1 enter =COUNTA(A1:D1) and filter on column E <= 2 If the words are in one cell per row (e.g., in column A), separated by commas, put this in B1: =COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Filter column B on B <= 2 In article , Chops wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
Hi,
in a dummy column enter this formula and then autofilter on the number 1. =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) -- Thanks, Shane Devenshire "Chops" wrote: Ok I will try this, so do I click on column B and then what do I do. Sorry I am so new to this. Thanx Carolin "JE McGimpsey" wrote: No, but a modification of the third one will: =COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) In article , Chops wrote: they are in the sme row, but not seperated by commas there is just a space between the words. Does the second formula still work. Thanx Carolin "JE McGimpsey" wrote: If the words are in separate cells, (e.g., columns A:D), and A is filled first, then B,C,D, then one way: Autofilter the list, then in the column C autofilter dropdown, choose "Show Blanks" If the words are in separate cells, and the columns may be randomly filled or blank, in E1 enter =COUNTA(A1:D1) and filter on column E <= 2 If the words are in one cell per row (e.g., in column A), separated by commas, put this in B1: =COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Filter column B on B <= 2 In article , Chops wrote: I have an excel file with 18000 keywords in it. Some of the lines are up to 4 words per line. How do I sort it so I can only see the lines that have 1 or 2 keywords on them?? |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
How do I sort a excel file by how many words ie all lines with
Assuming the values are in column A.
1) Click on B1 2) Enter the formula below 3) Grab the fill-handle of B1 (lower right corner), and drag down as far as required 4) Select columns A:B 5) Choose Data/Autofilter 6) In the B1 dropdown, choose Custom Filter 7) Using the dropdown and combobox, choose "is less than or equal to" 2 8) Click OK In article , Chops wrote: Ok I will try this, so do I click on column B and then what do I do. Sorry I am so new to this. Thanx Carolin "JE McGimpsey" wrote: No, but a modification of the third one will: =COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why can't I sort new Excel entries after I exceed 1115 lines? | Excel Discussion (Misc queries) | |||
| how do i up load an ascii file to excel to do labels in words | Excel Discussion (Misc queries) | |||
| Help: I need a macro to add words every 3 lines | Excel Discussion (Misc queries) | |||
| How do you sort words in Excel by the number of letters in a word | New Users to Excel | |||
| How do I add header information to an Excel file? In other words,. | New Users to Excel | |||
Hybrid Mode
