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