VBA Count Number of Words in a String with 5+ Characters

[CatLadee]

Hi friends,

I am not sure how to answer this question for a VBA class i'm taking. How would I count only words with 5+ letters? Thank you! CatLadee

msg = "Total words: " & UBound(arrWords) - LBound(arrWords) + 1 & vbNewLine & "Large words:" & [B]?????[/B]
MsgBox msg

 

[Rick Rothstein]

That is a hard question to answer as we do not know what you have learned to date. If you know about the Split function and loops, that would be the simplest way to do it... split the text using a space delimiter, iterate each array element in a loop and test the length of each array element during the iteration, increment a counter for those elements meeting your criteria.

 

[Peter_SSs]

I agree with Rick that it depends what you have learned so far.
Perhaps another option, if you haven't dealt with Split, is to also use a loop with the InStr() function to find the position of successive blanks in the string and compare those positions.

 

[CatLadee]

I agree with Rick that it depends what you have learned so far.
Perhaps another option, if you haven't dealt with Split, is to also use a loop with the InStr() function to find the position of successive blanks in the string and compare those positions.

Thanks. I am familiar with all the concepts mentioned on a very beginner level. This is my 5th week in a VBA class for school.

This is where I am now - The top codes seems to be counting the words correctly. The bottom code is supposed to be counting the words in the string with more than 5 characters (5 total). I tried to integrate InStr but am not sure how to make it say "count only if there are more than 5 characters" and it's returning 6 without that logic and I'm not sure why. I couldn't used InStr with an array so I created a new variable. eek!

    Open fileName For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    i = 0
    Do Until EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , dataLine
        arrWords() = Split(dataLine, " ")
        For i = 0 To UBound(arrWords)
            nTotalWords = Len(arrWords(i))
        Next i
        i = i + 1
    Loop
    
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
      Open fileName For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    i = 0
    Do Until EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , dataLine
        nLargeWords = InStr(dataLine, " ")
        For i = 0 To UBound(arrWords)
            msg = nLargeWords
        Next i
        i = i + 1
    Loop
    
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    msg = "Total words: " & nTotalWords & vbNewLine & "Large words: " & nLargeWords

 

[Rick Rothstein]

Thanks. I am familiar with all the concepts mentioned on a very beginner level. This is my 5th week in a VBA class for school.

This is where I am now - The top codes seems to be counting the words correctly. The bottom code is supposed to be counting the words in the string with more than 5 characters (5 total). I tried to integrate InStr but am not sure how to make it say "count only if there are more than 5 characters" and it's returning 6 without that logic and I'm not sure why. I couldn't used InStr with an array so I created a new variable. eek!

    Open fileName For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    i = 0
    Do Until EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , dataLine
        arrWords() = Split(dataLine, " ")
        For i = 0 To UBound(arrWords)
            [B][COLOR="#FF0000"]nTotalWords = Len(arrWords(i))[/COLOR][/B]
        Next i
        i = i + 1
    Loop
    
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
[/QUOTE]
You need to test the length of the array element you are currently iterating and, if it is more than 5 characters long, increase your counter variable (which I assume is nTotalWords) by 1...

If Len(arrWords(i)) > 5 Then nTotalWords = nTotalWords + 1

 

[CatLadee]

You need to test the length of the array element you are currently iterating and, if it is more than 5 characters long, increase your counter variable (which I assume is nTotalWords) by 1...

If Len(arrWords(i)) > 5 Then nTotalWords = nTotalWords + 1

Worked perfectly - thank you very much!

 

[Rick Rothstein]

Worked perfectly - thank you very much!

More important than that (I knew it would work)... do you understand how it works.

thank you very much!

You are quite welcome!

 

[CatLadee]

Yes I do! I appreciate your help!