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


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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
This applies to Dell but may also apply to other manufacturers as well. We ran across a few machines that just dropped recently it trust relationship with the server. After doing the basic removing and joining the domain again, it changed to No logo…
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

564 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