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

Assuming you want to put the cell totals in Column E, put this formula in cell E2 and copy it down to the end of your data...

=LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1

For the grand total, use the SUM function. So if the last row with data was Row 99, then you would use this formula...

=SUM(E2:E99)

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Rick, many thanks for coming back to me so quickly. Much appreciated.

I have used the formula you have very kindly provided and it does almost resolve the issue.

However, I have noticed the formula is also counting "empty" cells as "1".

I should have advised there could be 'empty' cells in D2 to D32. Apologies. ​

Can those cells which are 'empty' be excluded?

Thank you again

 

[Rick Rothstein]

Re: Counting alpha and numercial data in a cell

I have used the formula you have very kindly provided and it does almost resolve the issue.

However, I have noticed the formula is also counting "empty" cells as "1".

I should have advised there could be 'empty' cells in D2 to D32. Apologies. ​

Can those cells which are 'empty' be excluded?

Give this a try...

=IF(LEN(D2),LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1,0)

which will show 0 for empty cells. If you would rather show nothing for them, then give this formula a try instead...

=IF(LEN(D2),LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1,"")

 

[admiral100]

Re: Counting alpha and numercial data in a cell

Hi,

Another approach :

Ctrl+Shift+Enter NOT just Enter

E2 =SUM(IFERROR(SEARCH(",",MID(D2,ROW($A$1:$A$300),1)),0),1)

For the grand total :

=SUM(E2:E99)

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Rick, thank you for your response and updated formula. It works just great. Thank you for your time and effort. Much appreciated.​

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Thank you for the alternative approach. I have used your formula and it works fine with the exception that it counts those cells which are empty. How do I amend so that empty cells are "0" or 'blank'. Thanks again

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Rick, in my cell D2 etc some of the data occurrences are in BOLD text. Is there a formula that would also just count the number of data occurrences that are in BOLD only?


There could be several BOLD occurrences in any one cell e.g D2. For example;


D2 A1111, A2222, A3333, A44,A5555, A6666, A777, A8888
D3 etc

Again there could be no data occurrences or BOLD data occurrences in D2 to D32

The sum for the number of BOLD entries would be 3

Many thanks for your consideration.

 

[Rick Rothstein]

Re: Counting alpha and numercial data in a cell

Rick, in my cell D2 etc some of the data occurrences are in BOLD text. Is there a formula that would also just count the number of data occurrences that are in BOLD only?

As far as I know, you cannot do that with a formula; however, there is a VB solution for what you want. The following is a function that can be called from other VB code or used on a worksheet. The function takes one argument... the cell (as a range). So, to call this from other VB code, you would do something like this...

CountOfBoldWords = BoldCount(Range("D2"))

To call this from within a worksheet formula, you would do this...

=BoldCount(D2)

Here is the code...

Function BoldCount(Rng As Range) As Long
  Dim x As Long, Txt As String
  Txt = Rng.Characters.Text
  For x = 1 To Len(Txt)
    If Not Rng.Characters(x, 1).Font.Bold Then Mid(Txt, x) = " "
  Next
  BoldCount = 1 + UBound(Split(Application.Trim(Txt)))
End Function

HOW TO INSTALL
------------------------------------
To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Manexcel]

Re: Counting alpha and numercial data in a cell

Hi Rick, the function you have provided works very well. I have created a module copied the code into it and used the

=boldcount(D2) function​

in cell P2 from within a worksheet formula. All is great. Many thanks and very much appreciated.

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.

You mention another approach by using

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

I am not a VBA programmer so I am not sure where to put this code

and how and where the count number appears on the 18 worksheets.

Can you assist?

Thank you.
​