Counting values in single cell - Macro

Hello all - I am pretty new to VBA programming (just started 2 days ago). I was wondering if what I am trying to do is possible, I searched around online quite a bit before posting. Here's what I want to achieve. I am trying to do this is excel formulas first, and then create a macro out of it.

I have a big set of data with two columns of items that I would like to count. For example, my data that I want to count is in cell A1 and A2.

Say I have this:
Cell A1:::: L9, L10, L56, L23
Cell B1:::: L99, L41, L5

I want to count the number of "L?" that I have -- So for the example above, the count should be "7".

Or if I have the below data, the count should be "5".
Cell A1:::: L39, L15, L26, L43
Cell B1:::: L94

Or if I have the below data, the count should be "1".
Cell A1:::: L4
Cell B1:::: (blank)

I tried using the formula below by looking at values separated by commas, but as you can see...some of the data (particularly the last value) in a cell is not separated by a comma, so this formula did not work. Also if I have a single data point, this formula does not work.

=LEN(TRIM(H7))-LEN(SUBSTITUTE(TRIM(A1),",",""))+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",","")) ....and similarly for A2.

I tried doing a "COUNTIF(A1,"L*"), but this does not count repetitive values.


Any tips that can help me out with this? I would like to create a macro with a click button that achieves this function.

Thanks in advance!

To find the number of L's in A1, you can use this:

=LEN(A1)-LEN(SUBSTITUTE(A1,"L",""))

You seem to be making it very complicated.

Hope this helps.

Pete

Thank you for your quick response!

I have a data table of over 400 lines and the values do not always start with "L", it could be any "XX" or "XXX"

I have created everything else that I want to achieve in this macro with a click button, this is the last part that is giving me some trouble.

Pete - That makes so much more sense, that actually works in the formula!

What if the values do not always start with "L" ?

Try it - it should make no difference, as any L within A1 is removed by the second term, wherever it occurs within A1.

Hope this helps.

Pete

Your formula does work, but let's say I have this scenario and the formula is in column C (to count)

Cell A1:::: L9, L10, L56, L23
Cell B1:::: L99, L41, L5
= Cell C1: 7


Cell A2:::: R9, R10, R56, R23
Cell B2:::: R99,
= Cell C2: 0
(I can change C2 to search for "R" instead of "L"), but this would be tedious for all cells if they all have different starting letters)

Cell A3:::: C9, C10, C56, C23
Cell B3::::
= Cell C3: 4
(I can change C3 to search for "C" instead of "R"), but this would be tedious for all cells if they all have different starting letters)

Thanks so much for your help!!!

Instead of explicitly using "L" within the formula that I gave you earlier, you can change this to a cell reference, where that cell contains the letter that you want to look for, or if you always want to look for the first letter in the string you can change the formula to this in C1:

=LEN(A1)-LEN(SUBSTITUTE(A1,LEFT(TRIM(A1)),""))

and then copy down.

Hope this helps.

Pete

mehmetcik,
Thanks for your reply!

I actually tried counting the commas initially...but for instances where there is no comma in cell B1 (when there is only one value), this would not work, for example:

In the case below, the answer should be 5, but I get 4....because cell B1 does not have a comma and only 1 value.

Cell A1:::: R9, R10, R56, R23
Cell B1:::: R99

If you want to count commas instead, then you can do this:

=LEN(","&A1)-LEN(SUBSTITUTE(","&A1,",",""))

Hope this helps.

Pete

AHH! that works! THANK YOU SO MUCH PETE!!!

That's perfect!

Can I directly implement this as a macro now?

I don't know which one you mean, as I just gave you two approaches in quick succession, but yes you can. You need to double-up all the " in the formula.

Hope this helps.

Pete

I used this one:

=LEN(A1)-LEN(SUBSTITUTE(A1,LEFT(TRIM(A1)),""))

What do you mean double up the " ?

Turn the macro recorder on, record a macro while you put that formula in a cell, stop recording, then view the result. It will probably have used the .FormulaR1C1 construct, but can you also see what has happened to the "" at the end of the formula?

Pete