?
Solved

Excel formula that can count the words in the adjacent cell.

Posted on 2011-10-07
9
Medium Priority
?
283 Views
Last Modified: 2012-05-12
Excel formula that can count the words in the adjacent cell.
Say i put it in C2 it has to check B2 and give me the word count
please help need this URGENT
0
Comment
Question by:bsharath
9 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 1000 total points
ID: 36932493
Assuming that you can count the spaces and add 1 try this formula in C2

=IF(TRIM(B2)="",0,LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2)," ",""))+1)

regards, barry
0
 
LVL 11

Author Comment

by:bsharath
ID: 36932765
thanks
The counts seem to be wrong does it count spaces?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36932816
TRIM takes out excess spaces (just in case you have some at the end....or two spaces between a word) and then the formula counts the spaces left and adds 1 - that would normally give an accurate word count. Can you give an example that's not working, do you have some other character separating the words?

regards, barry
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 50

Expert Comment

by:barry houdini
ID: 36932899
Here are some examples....

barry
27385877.xls
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 36933781
barry's formula ought to work, although it is possible that what you think are spaces are not really spaces.

For example, if some or all of the spaces are really ANSI 160...

=IF(TRIM(SUBSTITUTE(B2,CHAR(160)," "))="",0,LEN(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(B2,CHAR(160)," "))," ",""))+1)
0
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 36934176
Can I suggest that the questioner provide some examples where it fails?
0
 
LVL 11

Author Comment

by:bsharath
ID: 36935407
Hi All.. Thanks
The formula does work

Say i get the results as 96 words and when i paste the words into word file it shows me 105. So was wondering where are those 11 words difference coming from
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36935534
Please post an example excel with this happening so we can have a look

Michael
0
 
LVL 11

Author Comment

by:bsharath
ID: 37283790
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question