LEN and Numeric Formula

[Paula]

Hi,

I am trying to identify in a cell if the entry is 6 characters long and the
characters are all numbers, the answer is yes, otherwise no. can you advise
if this is possible?

Many thanks

Paula

 

[Gary''s Student]

=AND((LEN(A1)=6),ISNUMBER(A1))

 

[John C]

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.

 

[Harald Staff]

Question now is if - and . satisfy "characters are all numbers". In other
words why the h and what is this for? A simple >=100000 might do in the
stricter understanding of the task.

Best wishes Harald

 

[Gary''s Student]

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(A1,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!

 

[John C]

I like Gary's UDF idea (I didn't test it mind you . But the issue with the

=100000 is what if the cell contains 000444?

Perhaps more input from the OP is needed at this point.

 

[Paula]

Hi, As it happens the first seemed to work, I think this is because my
numbers are always 999999 pattern, no dots spaces or - numbers as it is a
staff identifier. Can I be greedy with another question. Can I ask can I add
another condition of if another cell equals Z combined with this, I'm trying
and can't seem to get it right?

Thanks for all your help.

Paula

 

[Paula]

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]

=OR(AND((LEN(A1)=6),ISNUMBER(A1)),(E1="Z"))

So even if A1 is not quite good enough, E1 can save the day!

 

[John C]

Look, up in the sky, it's a bird, it's a plane, it's E1man! (or E1woman

 

[Gary''s Student]

Perhaps a new superhero!