| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell References | Excel Discussion (Misc queries) | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| cell color index comparison | New Users to Excel | |||
Linear Mode
