Pages in topic:   [1 2] >
Word count in Excel?
Thread poster: Kim Metzger
Kim Metzger
Kim Metzger  Identity Verified
Mexico
Local time: 01:57
German to English
May 13, 2004

Can anyone tell me if it's possible to do an automatic word count for a document in Excel?

 
Sabry Hameed
Sabry Hameed  Identity Verified
Egypt
Local time: 09:57
Member (2004)
Persian (Farsi) to Arabic
+ ...
copy the text and paste it into Word May 13, 2004

You can copy the text and paste it into word.

It usually works with me

Sabry


 
Kim Metzger
Kim Metzger  Identity Verified
Mexico
Local time: 01:57
German to English
TOPIC STARTER
Word count in Excel May 14, 2004

Thank you very much, sabrygameel. It worked for me too.
Best wishes, Kim


 
Samuel Murray
Samuel Murray  Identity Verified
Netherlands
Local time: 08:57
Member (2006)
English to Afrikaans
+ ...
How about 100 worksheets? May 14, 2004

sabrygameel wrote:
You can copy the text and paste it into word.


Any idea how to do this quickly when there are hundreds of little worksheets inside the Excel document?


 
Bob Kerns (X)
Bob Kerns (X)  Identity Verified
Germany
Local time: 08:57
German to English
Practicount & Invoice May 14, 2004

Samuel Murray-Smit wrote:

Any idea how to do this quickly when there are hundreds of little worksheets inside the Excel document?


Practicount (www.practiline.com) can count multiple worksheets in an Excel file. It's also capable of counting characters, words and lines in PowerPoint and PDF files. One of the best investments I ever made.


 
djack
djack  Identity Verified
Russian Federation
Local time: 10:57
English to Russian
+ ...
PractiCount does not count words in Excel files correctly May 14, 2004

I made this investment too, and the way this application counts words and characters in Word files now suits me perfectly. Nevertheless, I found that, in doing the same job for Excel files, the program gives a 30% "discount", i.e. the total count is only 2/3 of the count obtained by copying and pasting the text from Excel into Word.
So, beware!


 
Kaan Yesilyurt
Kaan Yesilyurt
Local time: 10:57
Turkish to English
+ ...
Lets have a look May 15, 2004

Dear kim;
As i understood you want excell to print out the count of words in a cell or a sheet...
i.e. its written "Wellcome to the real world" in the A1 cell. The word count in the A1 cell can be calculated with the following equation.

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

when you enter the above equation in a2 cell you will get 4 which is the number of words in a1 cell.


[Edited at 2004-05-15 01:30]


 
Mathew Robinson
Mathew Robinson
United Kingdom
Local time: 07:57
English
Multiple Worksheets May 17, 2004

In Word, up to version XP, you can use 'open' then change the 'Files of type' to 'Excel Worksheet' and open the entire Excel file no matter how many worksheets are in it. You might need to install the 'Conversion Tools' from the original CD if you didn't do so when first installing Office.

For some reason, it seems the Excel filter has been removed in Word 2003.


 
sphinx7
sphinx7
China
Local time: 15:57
Chinese to English
other suggestions? May 19, 2004

any other suggestions? i also want to know this

 
EKM
EKM
Sweden
Local time: 08:57
English to Swedish
+ ...
Thank you very much, Techtrans! Brilliant! May 20, 2004

Multiple Worksheets May 17

In Word, up to version XP, you can use 'open' then change the 'Files of type' to 'Excel Worksheet' and open the entire Excel file no matter how many worksheets are in it. You might need to install the 'Conversion Tools' from the original CD if you didn't do so when first installing Office.

For some reason, it seems the Excel filter has ... See more
Multiple Worksheets May 17

In Word, up to version XP, you can use 'open' then change the 'Files of type' to 'Excel Worksheet' and open the entire Excel file no matter how many worksheets are in it. You might need to install the 'Conversion Tools' from the original CD if you didn't do so when first installing Office.

For some reason, it seems the Excel filter has been removed in Word 2003.


I just tried it, and it works perfectly. That will save me (and probably many others) lots of time!
Collapse


 
Frank Hesse
Frank Hesse  Identity Verified
Mexico
Local time: 01:57
Dutch to English
+ ...
use "recover text from any file" in word 2003 May 26, 2004

TechTrans wrote:

In Word, up to version XP, you can use 'open' then change the 'Files of type' to 'Excel Worksheet' and open the entire Excel file no matter how many worksheets are in it. You might need to install the 'Conversion Tools' from the original CD if you didn't do so when first installing Office.

For some reason, it seems the Excel filter has been removed in Word 2003.




I tried using OPEN, then 'Files of type' and 'recover text from any file' in word 2003. I am not sure how accurate the wordcount is, however.


 
Freebird
Freebird
Japan
Local time: 16:57
Japanese to English
+ ...
With languages that do not have spacing between words May 28, 2004

mukaanyes wrote:

Dear kim;
As i understood you want excell to print out the count of words in a cell or a sheet...
i.e. its written "Wellcome to the real world" in the A1 cell. The word count in the A1 cell can be calculated with the following equation.

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

when you enter the above equation in a2 cell you will get 4 which is the number of words in a1 cell.


[Edited at 2004-05-15 01:30]


Hi,

In Japanese language there is no spacing between words. In this case what changes would be required in the above mentioned equation change, so that the words could be counted

Regard
Freebird


 
Kaan Yesilyurt
Kaan Yesilyurt
Local time: 10:57
Turkish to English
+ ...
Just replace space with what japanese use between words May 29, 2004

Freebird wrote:

Hi,

In Japanese language there is no spacing between words. In this case what changes would be required in the above mentioned equation change, so that the words could be counted

Regard
Freebird


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

Hi, The above function counts words which are delimited with spaces. but you say there is no space between japanese words so just replace \" \" with what is japanese words are delimited. Thats, if you use colon (:) between japanese words the function would be;
=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),\":\",\"\"))+1)


 
Andrew Wille (X)
Andrew Wille (X)
Local time: 16:57
Japanese to English
Japanese word count is very tricky Jun 2, 2004

In Japanese particles (ha, ga, wo, ni etc.) typically separate the words, and define topic, subject, object, indirect object etc. This is pretty much in effect what Mukaanyes suggested above - particles can act as delimiters.

However, it also depends on what you want to call a word. For instance a very long kanji compound that is unseparated by particles will be a number of words in English. AND In other cases, depending on the style of the content, some particles may be omitte
... See more
In Japanese particles (ha, ga, wo, ni etc.) typically separate the words, and define topic, subject, object, indirect object etc. This is pretty much in effect what Mukaanyes suggested above - particles can act as delimiters.

However, it also depends on what you want to call a word. For instance a very long kanji compound that is unseparated by particles will be a number of words in English. AND In other cases, depending on the style of the content, some particles may be omitted. It is therefore really hard to get a computer to differentiate definitely between the start and end of a word.

It gets to be quite a pain - which explains why many people quote by the number of characters, or number of pages!!

If you want a word count I suggest the following methods. These methods do not, however, overcome the spralling kanji compound problem. And let's not even consider the spralling kanji compound that has been contracted into two characters, but will expand on translation into something resembling a compilation of all the plays by Shakespear.

* APPROACH 1
I've been playing around with some VBA to separate words in sentences based on the particles at a very basic level. Particles are delimiters. Brackets in the sentence also act as delimiters, as does any punctuation.

Basically what you need to do is
1) Store the delimiters in a list
2) Take each character of your source sentence and compare it with each entry in your delimiter list.
3) Each time a particle in the source sentence is identified then count a word

This is the most effective method.

HOWEVER, you have to think about it a bit more, because the particles, which are in hiragana, also occur in words written in hiragana. So basically, delimiters can in fact occur in Japanese as both delimiters and characters within the same sentence.

What you can do to overcome this is make a list of commonly occurring hiragana words and have your macro look at this aswell. That is, ensure that it doesn't consider characters which are actually parts of words to be delimiters.

There are also single character delimiters such as "ha" and "de", but also double character ones such as "deha" and "niha". You will need to differentiate between these. In short you have to consider not only the delimiters but what comes before and after them, otherwise you will most likely overcount the number of words.

Another thing you may want to consider is whether you want to call "ni" a marker or a word, since it does act as a preposition/denotes direction/location etc and would be a word in English.

*APPROACH 2
Another separate approach is to use the Split command. This will break your sentence into parts at a given particle and store the parts in an array. You can then split the array contents into more arrays for the other particles. Eventually, after a lot of frustration, you will work down to word level. At this stage you get the size of the arrays using the Ubound command, and use the result to work out how many words there are. This was infact the first approach I considered but it turns out to be more complex than the one I mentioned above, since you have to define particle priorities, and split up phrases and clauses that constitute the sentence. I do not advise it!

*APPROACH 3
I haven't investigated this one yet, but I'm sure it is possible.
Have the macro return whether the characters are kanji, hiragana, or katakana. Since these variations will indicate changes the start and end of words.


In any case, you will still be faced with the same problem of hiragana characters which can be both delimiters and part of a word. You have therefore to check not just for delimiters, but also what comes before and after them. Even if you do this, you will still have the spralling unresolved kanji combos, and katakana combos.


PS If anyone knows an approach where you click on the tool bar and select word count please let me know!
Collapse


 
Pages in topic:   [1 2] >


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Word count in Excel?







TM-Town
Manage your TMs and Terms ... and boost your translation business

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

More info »
CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »