|
|
此文章由 猪猪的宝贝 原创或转贴，不代表本站立场和观点，版权归 oursteps.com.au 和作者 猪猪的宝贝 所有！转贴必须注明作者、出处和本声明，并保持内容完整
thanks LS, but I dont understand... kan bu dong...
Word Count
Unlike MS Word, Excel doesn't have a built in method for counting the number of words in a spreadsheet. However, you can count the number of words with a simple VBA macro.
Sub CountWords()
Dim WordCount As Long
Dim Rng As Range
Dim S As String
Dim N As Long
For Each Rng In ActiveSheet.UsedRange.Cells
S = Application.WorksheetFunction.Trim(Rng.Text)
N = 0
If S <> vbNullString Then
N = Len(S) - Len(Replace(S, " ", "")) + 1
End If
WordCount = WordCount + N
Next Rng
MsgBox "Words In ActiveSheet Sheet: " & Format(WordCount,"#,##0")
End Sub
To count the words in a single cell, you can use the following formula:
=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)
This works by first using TRIM to convert multiple spaces to single spaces and remove any leading or trailing spaces. Then it subtracts from the Length of A1 the Length of A1 after all spaces have been removed (SUBSTITUE). The difference is the number of spaces in A1. Add 1 to that and you get the number of words. The same function in VBA is shown below. Note that it uses Excel's worksheet function TRIM, not VBA's Trim function. The two are not the same. Both functions remove leading and trailing spaces, but Excel's TRIM changes multiple spaces within the string to single spaces, while VBA's Trim function leaves multiple spaces within the string intact. For example,
Debug.Print Application.WorksheetFunction.Trim(" a b c ")
prints
a b c
while
Debug.Print Trim(" a b c ")
prints
a b c
Function WordsInCell(Cell As Range) As Variant
If Cell.Cells.Count <> 1 Then
'''''''''''''''''''''''''''''''''''
' return #VALUE if there is more
' than one cell referenced by Cell.
'''''''''''''''''''''''''''''''''''
WordsInCell = CVErr(xlErrValue)
Exit Function
End If
If Len(Cell.Text) > 0 Then
With Application.WorksheetFunction
WordsInCell = Len(.Trim(Cell.Text)) - _
Len(.Substitute(.Trim(Cell), " ", "")) + 1
End With
Else
WordsInCell = 0
End If
End Function |
|
Box Hill的银行 (2005-10-5) goldenapple 
发表于 2010-8-9 10:32
