Verifying value of a cell
 

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

Sure you can!

the only problem, that i saw right now is, that istext interprets a number
also as text, so there you could have some difficulties!
You might want to have a closer look at that part!

Cheers Carlo

"El Criollo" wrote:

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

Yes, I am having difficulties!!! :(
since ab123c456 is text!
can we say 1st, 2nd and 6th caracters ARE NOT NUMBERS?? or =A-z/a-z?? HOW?
cause I cant find a way around this validation...

"Carlo" wrote:

Sure you can!

the only problem, that i saw right now is, that istext interprets a number
also as text, so there you could have some difficulties!
You might want to have a closer look at that part!

Cheers Carlo

"El Criollo" wrote:

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

Ok, this one should work fine.
=IF(LEN(B2)5;IF(AND(CODE(UPPER(LEFT(B2;1)))64;CO DE(UPPER(LEFT(B2;1)))<90;CODE(UPPER(MID(B2;2;1))) 64;CODE(UPPER(MID(B2;2;1)))<90;CODE(UPPER(MID(B2;6 ;1)))64;CODE(UPPER(MID(B2;6;1)))<90;ISNUMBER(INT( MID(B2;3;3)&RIGHT(B2;3)));LEN(B2)=9);"OK";"Check Number");"Check Number")

hth, otherwise just ask

Carlo

"El Criollo" wrote:

Yes, I am having difficulties!!! :(
since ab123c456 is text!
can we say 1st, 2nd and 6th caracters ARE NOT NUMBERS?? or =A-z/a-z?? HOW?
cause I cant find a way around this validation...

"Carlo" wrote:

Sure you can!

the only problem, that i saw right now is, that istext interprets a number
also as text, so there you could have some difficulties!
You might want to have a closer look at that part!

Cheers Carlo

"El Criollo" wrote:

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

GREAT!!! WORKING SMOOTHLY !!!
MANY TNX !!!

"Carlo" wrote:

Ok, this one should work fine.
=IF(LEN(B2)5;IF(AND(CODE(UPPER(LEFT(B2;1)))64;CO DE(UPPER(LEFT(B2;1)))<90;CODE(UPPER(MID(B2;2;1))) 64;CODE(UPPER(MID(B2;2;1)))<90;CODE(UPPER(MID(B2;6 ;1)))64;CODE(UPPER(MID(B2;6;1)))<90;ISNUMBER(INT( MID(B2;3;3)&RIGHT(B2;3)));LEN(B2)=9);"OK";"Check Number");"Check Number")

hth, otherwise just ask

Carlo

"El Criollo" wrote:

Yes, I am having difficulties!!! :(
since ab123c456 is text!
can we say 1st, 2nd and 6th caracters ARE NOT NUMBERS?? or =A-z/a-z?? HOW?
cause I cant find a way around this validation...

"Carlo" wrote:

Sure you can!

the only problem, that i saw right now is, that istext interprets a number
also as text, so there you could have some difficulties!
You might want to have a closer look at that part!

Cheers Carlo

"El Criollo" wrote:

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx

Verifying value of a cell
 

No Problemo!

i think this would even be a little cleaner:
=IF(LEN(B2)<9;IF(AND(CODE(UPPER(LEFT(B2;1)))64;C ODE(UPPER(LEFT(B2;1)))<90;CODE(UPPER(MID(B2;2;1))) 64;CODE(UPPER(MID(B2;2;1)))<90;CODE(UPPER(MID(B2; 6;1)))64;CODE(UPPER(MID(B2;6;1)))<90;ISNUMBER(INT (MID(B2;3;3)&RIGHT(B2;3))));"OK";"Check Number");"Check Number")

no need to check the length twice :)

Cheers Carlo

"El Criollo" wrote:

GREAT!!! WORKING SMOOTHLY !!!
MANY TNX !!!

"Carlo" wrote:

Ok, this one should work fine.
=IF(LEN(B2)5;IF(AND(CODE(UPPER(LEFT(B2;1)))64;CO DE(UPPER(LEFT(B2;1)))<90;CODE(UPPER(MID(B2;2;1))) 64;CODE(UPPER(MID(B2;2;1)))<90;CODE(UPPER(MID(B2;6 ;1)))64;CODE(UPPER(MID(B2;6;1)))<90;ISNUMBER(INT( MID(B2;3;3)&RIGHT(B2;3)));LEN(B2)=9);"OK";"Check Number");"Check Number")

hth, otherwise just ask

Carlo

"El Criollo" wrote:

Yes, I am having difficulties!!! :(
since ab123c456 is text!
can we say 1st, 2nd and 6th caracters ARE NOT NUMBERS?? or =A-z/a-z?? HOW?
cause I cant find a way around this validation...

"Carlo" wrote:

Sure you can!

the only problem, that i saw right now is, that istext interprets a number
also as text, so there you could have some difficulties!
You might want to have a closer look at that part!

Cheers Carlo

"El Criollo" wrote:

THAT IS WORKING!!! Nice!!...
But... can I, instead of "ok", insert the formula that will calculate the
result??
Say...
=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);IF(OR(1=F2, F2=0), F2, 11-F2);"Check Number")

"Carlo" wrote:

hope this formula does what you need:

=IF(AND(ISTEXT(LEFT(B1;2)&MID(B1;6;1));ISNUMBER(IN T(MID(B1;3;3)&RIGHT(B1;3)));LEN(B1)=9);"OK";"Check Number")

cheers

Carlo

"El Criollo" wrote:

Hey there...
want to verify the structure of a value in a cel, since then I will use that
value for some other operations.
The value should allways be like AK456j912 That is: First and second and
sixth caracter a letter (Letters can be lower or upper), the rest numbers.
and want to return a messasge if it is not that structure. If it has the
structure, then apply the formula...
value of cell other operations result
as123z456 9 2
aaa439n0 #VALUE #VALUE
8u7j999o2 #VALUE "check number"
If structure wrong, then result is check number (Other operation will be
#VALUE)
Thanx