count the number of special characters in a excel worksheet cell

Posted on 2003-03-26
Medium Priority
Last Modified: 2008-03-17
i want to count the number of special characters in a excel sheet cell.  if a cell has the text "jimjim" in it and i want to count the number of "j"'s in the string, how would i do it.
Question by:chad201008
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
  • 3
  • 2
LVL 44

Accepted Solution

bruintje earned 300 total points
ID: 8215825
Hello chad201008,

you could try
-open the vb editor in excel with alt+f11
-next insert a new module
-paste this code

Public Function CountChars(ByVal rng As Range, ByVal s As String) As Integer
Dim strVal As String
Dim intString As Integer
Dim x As Integer
  CountChars = 0
  strVal = rng.Value     'Get string
  intString = Len(strVal)       'Length of String
  For x = 1 To intString        'Increment thru
      Select Case Mid(strVal, x, 1)
          Case s                'If it is a char sought for
              CountChars = CountChars + 1 'Add 1 to list
          Case Else         'Do nothing
      End Select
End Function

-save and close the editor
-now in a cell besides the one you use
-use your new function like



Author Comment

ID: 8218134
when i enter the code i get a error in the cell-#name? and i don't understand what i'm missing.  any ideas?
LVL 44

Expert Comment

ID: 8218296
you can pick up a sample here


has the function in a module and tested in a sheet

Author Comment

ID: 8218347
i put the module in the wrong place...my bad.  thanks, excellent answer
LVL 44

Expert Comment

ID: 8218459
cool thanks for the grade

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

If your app took Google’s lash recently, here are the 5 most likely reasons.
Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
This video shows how use content aware, what it’s used for, and when to use it over other tools.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.
Suggested Courses

801 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