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

x
?
Solved

count the number of special characters in a excel worksheet cell

Posted on 2003-03-26
5
Medium Priority
?
798 Views
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.
0
Comment
Question by:chad201008
  • 3
  • 2
5 Comments
 
LVL 44

Accepted Solution

by:
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
  Sheets("Sheet1").Select
  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
  Next
End Function

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

=CountChars(A1,"j")

HAGD:O)Bruintje
0
 

Author Comment

by:chad201008
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?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8218296
you can pick up a sample here

http://www.bredlum.com/ee_temp/xlCharCount.xls

has the function in a module and tested in a sheet
0
 

Author Comment

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

Expert Comment

by:bruintje
ID: 8218459
cool thanks for the grade
0

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