How do I sort a excel file by how many words ie all lines with 2 w

  • Thread starter Thread starter Chops
  • Start date Start date
C

Chops

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??
 
If I look at a typical row, are the keywords all in the same cell or does
each keyword have its own cell??
 
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
 
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
 
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
 
No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))
 
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
 
Hi,

in a dummy column enter this formula and then autofilter on the number 1.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
 
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
 
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.
 
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
 
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.
 
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.
 
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
 
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).
 
Back
Top