# Better Word Count in Excel

Better Word Count in Excel

Geneviève von Levetzow
Local time: 21:22
Member (2002)
French to German
+ ...
 Jul 23, 2003

A recent tip ("Counting Words in Excel") presented an Excel macro to count the number of words in a cell. But I found a much easier way to do this using a formula instead of a macro:

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

Counting Words in Excel

The formula works by taking the length of the text and subtracting the length of that same text minus the spaces between its words. If there is text, it adds one space for the initial word.

William "Ike" Eisenhauer

This particular tip inspired several readers to write in with nonmacro solutions, and Mr. Eisenhauer was the first to reach the goal. The key point in his formula is the difference between the TRIM worksheet function and the Trim function in a macro. The macro function Trim cuts off leading and trailing spaces from the string passed into it, period.

The TRIM worksheet function removes all spaces except for single spaces between words. The formula TRIM (SUBSTITUTE (A1,CHAR(10)," ")) replaces all line-feed characters with spaces and then trims the result to eliminate leading, trailing, and multiple spaces. The length of this formula's result minus the length of the same string with all spaces removed is the number of word-separating spaces in the input text.

This formula-based solution is more convenient than the original macro solution. To count the words in a cell using the macro, you have to select the cell and then invoke the macro from the menu or with a keyboard shortcut. The result is displayed in a message box and is not accessible for use in other formulas. With this formula-based solution, you have the freedom, for example, to count the words in each cell of a row and sum the counts.

http://www.pcmag.com/article2/0,4149,1191766,00.asp

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

Spain
Local time: 21:22
German to Spanish
+ ...
 Thank you Jul 24, 2003

Thank you Genevieve,

I think this is a very helpful and original way to count words in Excel, which is always a difficult task.

Greetings,

Pablo (ALTC)

 .tooltipX { position: relative; display: inline-block; } .tooltipX .tooltiptextX { visibility: hidden; width: 120px; background: linear-gradient(#1CA19F, #016B6A); color: #fff; text-align: center; border-radius: 6px; padding: 5px 0; position: absolute; z-index: 1; top: 125%; left: 50%; margin-left: -59px; opacity: 0; transition: opacity 0.3s; } .tooltipX .tooltiptextX::after { content: ""; position: absolute; bottom: 100%; left: 50%; margin-left: -6px; border-width: 5px; border-style: solid; border-color: transparent transparent #1CA19F transparent; } .tooltipX:hover .tooltiptextX { visibility: visible; opacity: 1; }

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

 Moderator(s) of this forum Maya Gorgoshidze [Call to this topic] Prachya Mruetusatorn [Call to this topic]

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

### Better Word Count in Excel

Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

WordFinder Unlimited
For clarity and excellence

WordFinder is the leading dictionary service that gives you the words you want anywhere, anytime. Access 260+ dictionaries from the world's leading dictionary publishers in virtually any device. Find the right word anywhere, anytime - online or offline.

P.O. Box 903
Syracuse, NY 13201
USA
+1-315-463-7323
ProZ.com Argentina
Calle 14 nro. 622 1/2 entre 44 y 45
La Plata (B1900AND), Buenos Aires
Argentina
+54-221-425-1266
ProZ.com Ukraine
6 Karazina St.
Kharkiv, 61002
Ukraine
+380 57 7281624