Solved

VBA - Excel Word count, do not count specific symbols

Posted on 2016-08-15
9
39 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
ID: 41756639
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
ID: 41757481
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
ID: 41757858
cellWords = UBound(Split(content, " ")) -1

Open in new window

0
 
LVL 1

Author Comment

by:Hakum
ID: 41759441
thank you i will try this and get back :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

Expert Comment

by:aikimark
ID: 41759529
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
ID: 41767098
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
ID: 41768646
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
ID: 41787780
@Hakum

Have you tested my code?
0
 
LVL 1

Author Closing Comment

by:Hakum
ID: 41789121
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office.Com /myAccount 9 36
vba excel2010 and 2013 2 36
Highlighting cells in Excel 9 16
Excel callender with date slider 5 22
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

948 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

19 Experts available now in Live!

Get 1:1 Help Now