Hi,

The first formula seemed to work, I think this is because my numbers are
always 999999 - no dots, spaces or - numbers, I am now trying to attach
another condition of IF cell E19 = Z it would also be true...

So I would want all 6 numerics in one cell to be true, but also even if that
cell was not 6 numberic if the cell in another column = Z it would still be
true?

I cant quite get to the right formula...

Paula


"Gary''s Student" wrote:

Good catch! How about if we test the length and also test that each of the
six "characters" is a digit?

First enter this UDF:

Function numeral(v As Variant) As Boolean
numeral = IsNumeric(v)
End Function

and then we can use something like:

=(LEN(A1)=6)*(numeral(MID(A1,1,1)))*(numeral(MID(A 1,2,1)))*(numeral(MID(A1,3,1)))*(numeral(MID(A1,4, 1)))*(numeral(MID(A1,5,1)))*(numeral(MID(A1,6,1)))


This is, admittedly, brute farse!
--
Gary''s Student - gsnu200809


"John C" wrote:

This won't handle -12345, or 123.45. If all characters are supposed to be
numbers, then these won't meet the critera. Modifying your formula:
=AND((LEN(SUBSTITUTE(SUBSTITUTE(A1,"-",""),".",""))=6),ISNUMBER(A1))

There is probably an easier way. And of course, I haven't thought about
scientific notation in the cell either. :)
--
** John C **

"Gary''s Student" wrote:

=AND((LEN(A1)=6),ISNUMBER(A1))
--
Gary''s Student - gsnu200809