Sort on number of words (words with least characters first)

I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.

A quick solution if you can stand doing some manual work.
1. Use a new sheet
2. Put your data in the FIRST column
3. Make a copy of this to the THIRD column
4. In the SECOND row, put a formula =counta(c1:xfd1) (count all nonempty cells from third to the last column) in all cells.
5. Mark the entire third column. Choose "Data"->"Data Tools"->"Text to columns" (Excel 2007, don't know exactly where in earlier versions, but they all have it). Choose "Delimiter" as space (" ").
6. Mark the entire sheet. Choose sort and sort by column B.
7. You now have all your data sorted as you wish.

Optionally, if you need it sorted in your original sheet, in the original sheet, make a new column, and BEFORE SORTING copy and paste your column TWO (i.e. number of words), paste special "only values". Now you can sort the entire sheet after this column, and when it is done, delete the column. Voila!

Few manual steps, but very easy! Good luck!

OK, list of values in column A:

Please Login or Register  to view this content.

In B1, put this formula then copy down:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 & "-" & LEN(A1)

Then highlight both columns, then sort by the B column. End result:

Please Login or Register  to view this content.

JB, since the output of your formula is text, the sort order will result in lists like the following if there are phrases of 10 words or more

1-1
1-2
11-2
12-5
2-5
20-33
3-15

I suggest changing the formula to

=VALUE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 & "." & LEN(A1))

so the result is a number and the sort order is right again.

Teylyn, you would still need to ensure your decimal LEN value was formatted to consistent number of 0's - else a 2 word string of 11 chars (2.11) would be < a 2 word string of 9 chars (2.9)

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 & "." & TEXT(LEN(A1),"0000")

Once the consistency is ensured the value/text issue is moot I think.

DO, good point.

but this

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 & "." & TEXT(LEN(A1),"0000")

Once the consistency is ensured the value/text issue is moot I think.

is still not perfect. The sort order will be something like

1.0004
11.0021
2.0003

So, nearing the perfect solution, try

=VALUE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 & "." & TEXT(LEN(A1),"0000"))

which will give numeric values that sort like this

1.0004
2.0003
11.0021

Sorry, yes, you are of course correct... must coerce resulting output (its 00:15 in my defence!)

Brilliant guys, works like magic -

(its 00:15 in my defence!)

I know that you can only be upped if extremely distracted or tired!!

Woo-hoo...Go team!