VBA - Excel Word count, do not count specific symbols

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

LVL 1
HakumAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
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
 
aikimarkCommented:
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
 
HakumAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
aikimarkCommented:
cellWords = UBound(Split(content, " ")) -1

Open in new window

0
 
HakumAuthor Commented:
thank you i will try this and get back :)
0
 
aikimarkCommented:
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
 
HakumAuthor Commented:
Sorry for the delay, really swumped with work :(

here is a sample file with only the text Sample230816.xlsx
0
 
aikimarkCommented:
@Hakum

Have you tested my code?
0
 
HakumAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.