Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA - Excel Word count, do not count specific symbols

Posted on 2016-08-15
9
Medium Priority
?
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

704 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