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

CatLadee

New Member
Joined
Sep 7, 2018
Messages
29
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 :confused:

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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.
 
Upvote 0
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!

Code:
    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
 
Upvote 0
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!

Code:
    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
 
Upvote 0
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!:)
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top