Help! I posted a problem earlier about creating a formula to cut off letter from a part number, but sometimes there are 2/3 letters at the end, or even none.
I have now tried to build a formula. This is what I want:
1) if the part no. is a number put it as it is. 2) if the part has 2 letters a the end, take the two letters off and give me the numbers. 3) if the second to last is a number, only take one off the end as the end one will then be a letter.
Here is the formula I have built, but it is not working, as I have the part number 3205624Z but it is returning 320562, but should be 3205624!
=IF(ISNUMBER(N154);N154;IF(ISNUMBER(MID(N154;LEN(N154)-1;1));MID(N154;1;LEN(N154)-1);IF(ISTEXT(RIGHT(N154;2));MID(N154;1;LEN(N154)-2);"NO")))
I hope this makes sense and someone can help.
Cheers
Helen
This message was edited by Helen on 2002-08-28 03:47
I have now tried to build a formula. This is what I want:
1) if the part no. is a number put it as it is. 2) if the part has 2 letters a the end, take the two letters off and give me the numbers. 3) if the second to last is a number, only take one off the end as the end one will then be a letter.
Here is the formula I have built, but it is not working, as I have the part number 3205624Z but it is returning 320562, but should be 3205624!
=IF(ISNUMBER(N154);N154;IF(ISNUMBER(MID(N154;LEN(N154)-1;1));MID(N154;1;LEN(N154)-1);IF(ISTEXT(RIGHT(N154;2));MID(N154;1;LEN(N154)-2);"NO")))
I hope this makes sense and someone can help.
Cheers
Helen
This message was edited by Helen on 2002-08-28 03:47