Count based on single number in string

[Basenji]

The column has data like this A1, 7*4*9; A2, 1*4; A3, 9*10. The numbers vary
as well as the length. The numbers are always whole numbers and never
negative. I need to count the number of cells that have a number less than
six immediately to the right of the first asterink from the left. The
asterink has nothing to do with multiplication. I tried to modify a formula
from my previous question but struck out. Thank you for your assistance.

 

[T. Valko]

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

 

[Basenji]

Yes

Will there *always* be at least 2 numbers in the cell?

1*4
1*4*10
1
4
1*
*

 

[T. Valko]

Try this...

Assumes no empty/blank cells.

=SUMPRODUCT(--(--MID(A1:A5,FIND("*",A1:A5)+1,FIND("~",SUBSTITUTE(A1:A5&"*","*","~",2))-FIND("*",A1:A5)-1)<6))

 

[Basenji]

Thank you. Unfortunately I am getting a value error. I have checked numerous
times for any typing errors but do not see any. What does the "~" refer to.
Also, I erred when I indicated that there will always be at least 2 numbers
in the cell. Upon rare occassions there may not be any numbers in the cell.
Hopefully that does not happen because it means sad news.

 

[T. Valko]

To get this to work properly we will need to know what are *all* the
possible types of cell entires. Try to list a good representative sample of
*all* possible cell entries.

Are there any empty cells in the range? Are there any formulas in the range
that return formula blanks?

 

[Basenji]

Samples of data include: 8*9, 7*8*9, 0*5*6. There will always be at least 2
numbers separated by an *. The numbers for any of the positions can be as low
as 0 (zero) and a maximum of 10. They are always whole numbers, no decimals
or fractions. There are no negative numbers. There may be empty cells within
the range of cells. There are no formulas. So whether the cell data is 5*6 or
2*4*6, the objective is to count the number of cells in which the second
number is less than six. So with these two cells of data the return is one.

Thank you for your assistance.

 

[T. Valko]

Ok, try this array formula** :

=SUM(IF(ISNUMBER(FIND("*",A1:A10)),--(--MID(A1:A10,FIND("*",A1:A10)+1,FIND("~",SUBSTITUTE(A1:A10&"*","*","~",2))-FIND("*",A1:A10)-1)<6)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.