Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 10th 08, 03:12 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 9
Default 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   Report Post  
Old November 10th 08, 03:50 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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   Report Post  
Old November 10th 08, 04:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default 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), 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(A11)

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??

  #4   Report Post  
Old November 10th 08, 04:10 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 9
Default 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??


  #5   Report Post  
Old November 10th 08, 04:19 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 9
Default 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), 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(A11)

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   Report Post  
Old November 10th 08, 04:26 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default 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), 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(A11)

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   Report Post  
Old November 10th 08, 04:37 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 9
Default 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), 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(A11)

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   Report Post  
Old November 10th 08, 04:51 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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), 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(A11)

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   Report Post  
Old November 10th 08, 04:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default 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)," ",""))

  #10   Report Post  
Old November 10th 08, 05:03 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,624
Default 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," ",""))



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why can't I sort new Excel entries after I exceed 1115 lines? Merv Excel Discussion (Misc queries) 1 September 28th 07 03:23 PM
how do i up load an ascii file to excel to do labels in words dotties Excel Discussion (Misc queries) 3 November 8th 06 08:39 PM
Help: I need a macro to add words every 3 lines Mirandolle Excel Discussion (Misc queries) 11 August 7th 05 05:03 PM
How do you sort words in Excel by the number of letters in a word Kinger New Users to Excel 2 May 2nd 05 11:42 PM
How do I add header information to an Excel file? In other words,. Alvin M. New Users to Excel 2 January 26th 05 07:33 PM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017