Counting alpha and numerical data in a cell

Manexcel

Board Regular
Joined
Dec 28, 2015
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top