Counting number of words in a sentence

[kelly mort]

1. I am looking for a script to count the number of words in a sentence from a word file.

2. Also to count number of words in a given cell in Excel

Thanks for your attention
Kelly

 

[MARK858]

Re: Counting number os words in a sentence

2. Also to count number of words in a given cell in Excel

If there are only single spaces between the words...

Excel Workbook
	A	B
1	Also to count number of words in a given cell in Excel	12
2	AND	1
3	AND TWO	2
4	one two three four five	5
Sheet3

 

[kelly mort]

Re: Counting number os words in a sentence

Thanks Mark, can I have vba script for it?

Want to learn more
Thanks again
Kelly

 

[MARK858]

Re: Counting number os words in a sentence

Sub cWords()
    With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = _
        "=IF(LEN(TRIM(RC[-1]))-LEN(SUBSTITUTE(TRIM(RC[-1]),"" "",""""))>0,LEN(TRIM(RC[-1]))-LEN(SUBSTITUTE(TRIM(RC[-1]),"" "",""""))+1,1)"
        .Value = .Value
    End With
End Sub

 

[kelly mort]

ADVERTISEMENT

Re: Counting number os words in a sentence

Sub cWords()
    With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = _
        "=IF(LEN(TRIM(RC[-1]))-LEN(SUBSTITUTE(TRIM(RC[-1]),"" "",""""))>0,LEN(TRIM(RC[-1]))-LEN(SUBSTITUTE(TRIM(RC[-1]),"" "",""""))+1,1)"
        .Value = .Value
    End With
End Sub

Okay thanks
But I need explanation small
Thanks again
Kelly

 

[MARK858]

Re: Counting number os words in a sentence

Range is from B1 to B and last row in column A.
Not counting the Sub line....
2nd row puts in the same formula as post 2.
Row 3 changes it from formula to values.

 

[Macropod]

1. I am looking for a script to count the number of words in a sentence from a word file.

VBA has no idea what a grammatical sentence is. For example, consider the following:
Mr. Smith spent $1,234.56 at Dr. John's Grocery Store, to buy: 10.25kg of potatoes; 10kg of avocados; and 15.1kg of Mrs. Green's Mt. Pleasant macadamia nuts.
For you and me, that would count as one sentence; with VBA in Word it counts as 5 sentences.

 

[MARK858]

Seeing as it has been moved to the General forum because of the Word part of the question (and that board doesn't support HTML) the formula in post number 2 is

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

to save anyone trying to decipher it.