Improved Word Count Macro

[Amejin]

Hello all.

Please allow me to explain my situation. At my company I work with a lot of text-filled Excel files. In order to get a proper word count we're currently using the primitive method of "paste into Word and run a word count". I'd like to be able to do this from within Excel, and I found the macro below but have run into a snag.
The problem I have is that it ignores carriage returns and count the last word in the first line and the first word in the following line as one word. In the files I work with this can throw off the count by thousands of words.

So if I run this macro against a cell containing a single text string:
<My name is Amejin and
I like pizza and beer> - 10 words

The macro reads it as:
<My name is Amejin andI like pizza and beer> - 9 words

So, essentially I need a method of changing carriage returns into spaces so that the macro will return the proper count. If anyone has a solution, it would be greatly appreciated.

Please Login or Register  to view this content.

Thanks for your help!

[JBeaucaire]

Macros are fine, but I'm a fan of straight functions. So, here's a formula that will replace all those hidden carraige returns with spaces so your word counts are correct.

Please Login or Register  to view this content.

Meanwhile, the CHAR(10) is what you're trying to replace, so maybe you can snug that into your code and do it with your macro.

[JBeaucaire]

This macro ONLY does a replace on the Char(10):

Please Login or Register  to view this content.

Interestingly, after running this code, my multi-line cells still show in multi-lines, but the word counts are correct after running. Cool.
(Source)

You could insert this macro in your sheet separately, then just add a line to call the macro at the top of your current macro:

Please Login or Register  to view this content.

[Amejin]

Thanks for the help guys. I went ahead and came up with this after a few modifications to restrict it to a selection and it seems to work flawlessly. xD

Please Login or Register  to view this content.