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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
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)," ","")) |
How do I sort a excel file by how many words ie all lines with
Just a caution. This (a) assumes that there are no extraneous spaces
between, before, or after the words (which may well be a good assumption), and (b) won't find the rows with 1 keyword, which was in the original specification. In article , ShaneDevenshire wrote: in a dummy column enter this formula and then autofilter on the number 1. =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) |
How do I sort a excel file by how many words ie all lines with
This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over 18000 rows I am going thru. Thanx Carolin "JE McGimpsey" wrote: 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)," ","")) |
How do I sort a excel file by how many words ie all lines with
Say your formula is in B1, and you need to copy it down to B18000.
Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu Edit--Fill--Down, and voila. -- ** John C ** "Chops" wrote: This is perfect I just have one other question when I am doing the dragging is there an easier way then just dragging to the bottom because I have over 18000 rows I am going thru. Thanx Carolin "JE McGimpsey" wrote: 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)," ","")) |
How do I sort a excel file by how many words ie all lines with
Put the formula in B1
Hit F5 (edit|goto) and type: B1:B18000 (change that 18000 to the real last row) Now that range is selected. Hit F2 (to edit the formula in B1) but hit ctrl-enter to fill the selected range with the (adjusted for row) formula. Chops wrote: This is perfect I just have one other question when I am doing the dragging is there an easier way then just dragging to the bottom because I have over 18000 rows I am going thru. Thanx Carolin "JE McGimpsey" wrote: 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)," ","")) -- Dave Peterson |
How do I sort a excel file by how many words ie all lines with
Thanx
Like I sd I am new to this but I knew I could get the help. You all have been great now I am off to try it Again Thanx Carolin "John C" wrote: Say your formula is in B1, and you need to copy it down to B18000. Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu Edit--Fill--Down, and voila. -- ** John C ** "Chops" wrote: This is perfect I just have one other question when I am doing the dragging is there an easier way then just dragging to the bottom because I have over 18000 rows I am going thru. Thanx Carolin "JE McGimpsey" wrote: 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)," ","")) |
How do I sort a excel file by how many words ie all lines with
Be sure to press the YES button below to indicate your question has been
answered (specifically on JE McGimpsey's post that solved most of your question). -- ** John C ** "Chops" wrote: Thanx Like I sd I am new to this but I knew I could get the help. You all have been great now I am off to try it Again Thanx Carolin "John C" wrote: Say your formula is in B1, and you need to copy it down to B18000. Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu Edit--Fill--Down, and voila. -- ** John C ** "Chops" wrote: This is perfect I just have one other question when I am doing the dragging is there an easier way then just dragging to the bottom because I have over 18000 rows I am going thru. Thanx Carolin "JE McGimpsey" wrote: 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)," ","")) |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com