Need to count words in a cell, how to?
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
HI all i am doing some SEO writing (yuk) and I have to input my writing into cells, but how do I keep track of the # of words per cell, like I would in MS word? thx for any help on this!!!
10 replies
Sign in or join Beachball Help to reply.
- 0
sharknca ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
One possible solution would be to use Pages.
Insert a table and under the Edit menu is Writing Tools> Show Statistics. You can see the stats for a selected range of cells (or cell) or for the entire document.
- 0
Badunit ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I'm not sure exactly what you are doing but here's an idea to count the number of words in a cell. You would have to apply it to all cells and then add the results. Basically you take the difference in the length of two strings. The first string is the contents of the cell (after trimming any duplicate spaces). The second is the contents of the cell with all spaces removed. The assumption is all words are separated by one or more spaces. Count the spaces and you can determine how many words there are.
Space(s) but no words = zero words.
No spaces = zero or one word. A check for ISBLANK clears this up.
One space = two words.
etc.
For cell B2 this would be:
=IF(OR(ISBLANK(B2), LEN(TRIM(B2))=0), 0, 1+LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ","")))
I think this works but you may want to test it out a bit to be sure I didn't miss something.
What you would want to do (assuming you need to do this in Numbers, not Pages) is to create a separate table the same size as the one you are typing in and have this formula in each cell. All the references would be to table 1 (you would replace all "B2" with "Table 1::B2" in the formula then fill right and down to fill the entire table. You could use cell A1 for the sum of all these cells.
Message was edited by: Badunit
- 0
sharknca ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Clever. I was just staring at the list of Text functions thinking that there has to be a way to do this, but I wasn't getting very far.
- 0
coocooforcocoapuffs ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
thx all, good ideas but so complicated. maybe this would be easier: i just need to know when i have reached 20 words in any given cell. i can continuously count them but my eyes are already glazing over with the work itself. i was hoping for a simple formula that could be applied to all cells. hmmm...
- 0
Badunit ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
This is easily applied to all cells. I'd estimate you could be done in less than two minutes.
It would actually be more complicated (one more step) to know when you reach 20 words in a cell.
- 0
coocooforcocoapuffs ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
i guess i dont know how to apply this. my eyes glazed over when you mentioned another table with a reference from table 1. but i am brain dead now after finishing todays job (a 3x100 table) filled with 20 words per cell. maybe i should wait till morning to tackle the next one (another 3x100 table that needs to be filled), thx.
- 0
coocooforcocoapuffs ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Btw, the formula works great, i just can't figure out how to make it visual for an entire column of cells, cell by cell. for example, if i paste the contents of what I am writing into a test cell (B2 in this case) I get the word count. Ideally I would have some sorta visual that showed in real time how many words that I was typing in the cell as I went along. While I am dreaming, it would turn red if i went over, or something visual or audible would warn me. ha!
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Enter this slightly modified formula:
=IF(OR(ISBLANK(B), LEN(TRIM(B))=0), 0, 1+LEN(TRIM(B))-LEN(SUBSTITUTE(B," ","")))
And apply the 'Fill Down' feature.
Yvan KOENIG (from FRANCE jeudi 23 avril 2009 14:44:06)
- 0
Badunit ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Nope, it won't count the words until you have hit return, tab, or otherwise indicated that you are done typing in the cell. That's the way spreadsheets work.
While you could make a cell turn red if you go over twenty words, it can't tell you in real-time. It won't count the words while you are typing, only after you're done typing.
- 0
coocooforcocoapuffs ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Gotcha. i guess what i really want is the word count function like in ms word that could be assigned to a hot key. but the formula u provided helps! thx,