Count the occurrences of a character in a cell

Just got a question that I found interesting to mention here:

I am looking for following feature in ASAP Utilities:
Count number of user specified characters in a cell. e.g. I have a cell that has values "a,b,c,d,e". I would like to have a utility that tells me either
a. there are 4 "," characters
b. simply tell me that there are 5 characters separated by comma.

The first one could actually take a user input as a character for delimiter - it could be comma, semicolon, or any other character.
It would be nice if this feature were a part of excel / ASAP Utilities. Thanks.

You can do this with ASAP Utilities with the worksheet function ASAPCountChar:

=ASAPCountChar(SearchText, SearchValue)
This function returns the number of times the "SearchValue" occurs in the "SearchText". This way you can for example count the number of commas in a cell. This function is case sensitive.

For example:
count-character-in-string.gif

5 Responses to “Count the occurrences of a character in a cell”

  1. Hello, Bastien!
    Could you please share some of your wisdom :-)
    I'm an excel programmer and I would like to be able to lock my code from the users the way you do, with a password. How do you do that?
    Thank you for your ASAP utilities! :-)
    Masha

  2. Hello Masha,

    You can apply a password in the VBA project:
    1. In Microsoft Excel, open the project you want to protect.
    2. Open the Visual Basic Editor.
    3. In the Project Explorer, select your add-in project. (If the Project Explorer isn't displayed, click the Project Explorer button on the Visual Basic Editor toolbar.)
    4. On the Tools menu, click Properties.
    5. On the Protection tab in the Properties dialog box, select the Lock project for viewing check box.
    6. In the two text boxes under Password to view project properties, type the password you've chosen, and then click OK.

    Jan Karel Pieterse has written a nice article about creating add-ins. The following page shows how to apply a password:
    http://www.jkp-ads.com/Articles/DistributeMacro06.htm

    Kind regards,
    Bastien

  3. Ha! It's so easy! I feel ashamed I could not figure this out myself.
    VBA password will probably be safe enough in my case. I don't think my users will be able to break it.
    Thank you very much, Bastien!
    Masha

  4. Hi Bastien,

    May the couple live 1000 years !

    It will be quite handy if the ASAP can be used as an embedded object like the "MoreFunc.xll" add-in by the Laurent Loungre at
    http://xcell05.free.fr/english/index.html

    Regards
    CA Kanwaljit Singh Dhunna

  5. Hi Bastien,

    May the couple live 1000 years.
    Is there any option to List just the folder names (as an additional option we can specify the depth levels)

    Regards
    CA Kanwaljit Singh Dhunna
    India

Pinterest