时间戳

首页 > 解决方案 > 计算文本评论中大写单词的数量

arrays - 计算文本评论中大写单词的数量

问题描述

我想计算电影评论数据集 (A2:A1001) 中大写单词的数量，并将结果粘贴到 E 列。

我无法从电子表格上的 sub 获得任何回复。

Sub UppercaseWordCount()
    Dim ArraySplit() As String
    Dim X As Integer
    Dim Count As Integer
    Dim NextWord As String
    Dim Line As Integer
        Count = 0
        ArraySplit = Split("A2:A1001", " ")
        Line = 2
        
        'splitting each review into an array and using lower
        'and upper bounds with Ucase function to get a count
        'len function used to split up words from single characters
        
        For X = LBound(ArraySplit) To UBound(ArraySplit)
            NextWord = ArraySplit(X)
            If NextWord = UCase(NextWord) And Len(NextWord) >= 2 Then
                Count = Count + 1
            End If
        Next
        
        'calling the sub to column E with a count for each review
        Range("E" & Line).Value = Count
        Line = Line + 1
End Sub

标签： arraysexcelvbatext

解决方案

尝试这个。如果重复执行，您可以转换为自定义函数。

Sub UppercaseWordCount()

Dim ArraySplit() As String
Dim X As Long 'long better than integer
Dim Count As Long
Dim NextWord As String
Dim r As Range

'splitting each review into an array and using lower
'and upper bounds with Ucase function to get a count
'len function used to split up words from single characters
For Each r In Range("A2:A1001") 'loop through defined range
    ArraySplit = Split(r, " ")  'split each cell using space delimiter
    For X = LBound(ArraySplit) To UBound(ArraySplit) 'everything else as before
        NextWord = ArraySplit(X)
        If NextWord = UCase(NextWord) And Len(NextWord) >= 2 Then
            Count = Count + 1
        End If
    Next
    'calling the sub to column E with a count for each review
    r.Offset(, 4).Value = Count 'can use offset rather than defining a new variable
    Count = 0 'reset count
Next r

End Sub

推荐阅读