Counting alpha and numerical data in a cell

[Manexcel]

I have a sheet that has 4 columns and 32 rows. In Col D there are alpha and numerical data prefixed Annnn or Annn or Ann e.g. A1234 or A111 or A22. Each entry is separated by a ','.

I wish to count the number of occurrences of a Annn and / or Annn and/ or Ann in each specific cell, D2 to D32, and then sum the overall number of entries in D2 to D32.

As an example;

if Col D2 has A1234, A12 the total of this cell would be 2
If Col D3 has A1111, A2222, A3333, A333, A33 the total for this cell would be 5
etc

The sum overall would therefore be 7.

Is there a formula that could achieve this?

Many thanks for your help.

 

[Rick Rothstein]

Re: Counting alpha and numercial data in a cell

However, the data the function is counting is created each time by other VBA code and after this procedure is run I get #value ! messages in the cells the =boldcount(d2) function is in. I mitigate by resetting the =boldcount(D2) function down/ across 32 rows and 18 sheets.

I think I would need to see your other code in order to know exactly what is happening. Better would be if you could post your workbook (saved as a macro enabled workbook) to DropBox so that I (and others) could see exactly what is going on.

You mention another approach by using

CountOfBoldWords = BoldCount(Range("D2")) would this assist with the above issue?​

Maybe, but again, I think I would need to see your other code.

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Rick, my issue has been resolved. I have been advised by a colleague to insert some code in the existing VBA code:

Application.Calculation = xlAutomatic

Many thanks for all your time and effort. It is greatly appreciated.
​