Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA - Excel Word count, do not count specific symbols

Posted on 2016-08-15
9
Medium Priority
?
46 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 46

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 46

Expert Comment

by:aikimark
ID: 41757858
cellWords = UBound(Split(content, " ")) -1

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:Hakum
ID: 41759441
thank you i will try this and get back :)
0
 
LVL 46

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 46

Accepted Solution

by:
aikimark earned 2000 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 46

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
I came across an unsolved Outlook issue and here is my solution.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

581 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