I was doing some weird analysis on corporate mission statements and I had to count the number of words in each cell. That is when I realized there is no formula to calculate the number of words in a cell, I was too lazy to write an UDF for that, so I figured out a nearly perfect way to calculate no. of words in a cell using existing formulas.
If you want to calculate the no. of words in cell a1, then use the formula:
=len(a1)-len(substitute(a1," ",""))+1
What this does is, it calculates the number of spaces in a cell and adds 1 to it, thus almost finding the number of words in a cell. I say almost because, if a cell has “this blog rocks,really!”, this formula will calculate the words as 3, where as there are 4 words in there 🙂
But that is for you to think 😉
Also: Concatenate a bunch of cells using simple formula, Generate tag clouds in excel using vba, Master your IFs and BUTs
2 Responses to “Weighted Sorting in Excel ”
Just add a column calculating the "performance" or whatever is your criteria and sort by it? No?
have no patience to waste 13min. Save your time too.
Just thought I would mention, the "weird" custom sort behavior mentioned at 5:45 where "% return" doesn't appear to be sorting is because the "August Purchases" field has the sort preference and since these are such unique values, no additional sorting is possible on the "% return" field. If there were two entries that had the same "Customer Since" year AND the same "August Purchases" amount, THEN you would see a sorting of the "% return" on these two entries.