Solved

VBA - Excel Word count, do not count specific symbols

Posted on 2016-08-15
9
35 Views
Last Modified: 2016-09-07
Hi guys,

I need some help to amend this code so it pops and ask for which symbols or chars not to count aswell. like / or * or any other chars, there can also be more then 1 char to not count so it should seperate aswell

please advise

this is the code i have which works just fine

Sub CountWord()

Dim rng As Range, cell As Range
Dim cellWords, totalWords As Integer, content As String

Set rng = Selection
cellWords = 0
totalWords = 0


For Each cell In rng

    If Not cell.HasFormula Then
        content = cell.Value
        content = Trim(content)
        
        If content = "" Then
            cellWords = 0
        Else
            cellWords = 1
        End If
                
        Do While InStr(content, " ") > 0
            content = Mid(content, InStr(content, " "))
            content = Trim(content)
            cellWords = cellWords + 1
        Loop
                
        totalWords = totalWords + cellWords
    End If

Next cell

MsgBox totalWords & " words found in the selected range."

End Sub

Open in new window

0
Comment
Question by:Hakum
  • 5
  • 4
9 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
1. what is the relationship you define between "words" and "characters"?
2. Since you are delimiting your words in a very standard way, you should consider using the Split() function.
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Hi Aikimark,

Thank you for our reply,

1. characters is defined like as symbols like /*'?!# etc. and words are words in common
2. could you give me an examble i'm quit new in vba so not sure how i would do that.

thank you in advance
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
cellWords = UBound(Split(content, " ")) -1

Open in new window

0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
thank you i will try this and get back :)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
If you posted some sample text or workbook, that should help me understand your problem better.  It still isn't clear where you need to consider special characters.
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Sorry for the delay, really swumped with work :(

here is a sample file with only the text Sample230816.xlsx
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
Here's a first pass at a simplified solution.
Sub CountWord()

    Dim cell As Range
    Dim cellWords As Long, content As String
    
    cellWords = 0

    For Each cell In Selection

        If Not cell.HasFormula Then
            content = cell.Value
            content = Trim(content)
            
            cellWords = cellWords + UBound(Split(content)) + 1
    
        End If

    Next cell

    MsgBox cellWords & " words found in the selected range."

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@Hakum

Have you tested my code?
0
 
LVL 1

Author Closing Comment

by:Hakum
Comment Utility
Sadly i havnt been able to test as much as i would like but at the first glimse it seem to work perfectly for my purpos 😊 thank you sooo much and sorry for the delay!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now