LEN and Numeric Formula
 

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

LEN and Numeric Formula
 

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

LEN and Numeric Formula
 

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

LEN and Numeric Formula
 

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

"John C" <johnc@stateofdenial wrote in message
...
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

LEN and Numeric Formula
 

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

LEN and Numeric Formula
 

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.
--
** John C **

"Harald Staff" wrote:

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

"John C" <johnc@stateofdenial wrote in message
...
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

LEN and Numeric Formula
 

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


"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

LEN and Numeric Formula
 

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

LEN and Numeric Formula
 

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

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

--
Gary''s Student - gsnu200809

LEN and Numeric Formula
 

Look, up in the sky, it's a bird, it's a plane, it's E1man! (or E1woman :)
--
** John C **

"Gary''s Student" wrote:

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

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

--
Gary''s Student - gsnu200809

LEN and Numeric Formula
 

Perhaps a new superhero!
--
Gary''s Student - gsnu200809


"John C" wrote:

Look, up in the sky, it's a bird, it's a plane, it's E1man! (or E1woman :)
--
** John C **

"Gary''s Student" wrote:

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

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

--
Gary''s Student - gsnu200809