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.

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

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

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.

Yes

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.

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))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.

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" wrote:

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))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.

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?

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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" wrote:

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))

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Yes

"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
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.