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.

Sub NumberOfWords()

Dim NumberOfWord As Long
Dim RangeArea As Range
Dim Str As String
Dim Num As Long

For Each RangeArea In ActiveSheet.UsedRange.Cells
Str = Application.WorksheetFunction.Trim(RangeArea.Text)
Num = 0
If Str <> "" Then
Num = Len(Str) - Len(Replace(Str, " ", "")) + 1
End If

NumberOfWord = NumberOfWord + Num

Next RangeArea

MsgBox NumberOfWord

End Sub

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.

A1 =TextToTest
B1 =SUBSTITUTE(A1,CHAR(10)," ")
C1 =LEN(E1)-LEN(SUBSTITUTE(E1," ",""))+1

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):

Sub replacer()
Dim MyChar
MyChar = Chr(10)
   Worksheets("Sheet1").Columns("A:B").Replace _
   What:=MyChar, Replacement:=" ", _
   SearchOrder:=xlByColumns, MatchCase:=True
End Sub

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:

Sub NumberOfWords()

Dim NumberOfWord As Long
Dim RangeArea As Range
Dim Str As String
Dim Num As Long

    replacer

For Each RangeArea In ActiveSheet.UsedRange.Cells
....etc....

[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

Sub replacer()
Dim MyChar
Dim RangeArea As Range

Set RangeArea = Intersect(Selection, ActiveSheet.UsedRange)

For Each cell In RangeArea
MyChar = Chr(10)
   RangeArea.Replace _
   What:=MyChar, Replacement:=" ", _
   SearchOrder:=xlByColumns, MatchCase:=True
   
Next cell
   
End Sub