Formula problem

[Helen]

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

 

[XL-Dennis]

Helene,

Instead of revise Your formula I would like You to test following solution:

Bok1
	A	B	C	D
1	23465768AA	23465768
2	345678A	345678
3	123AA	123
4	AA123AA	123
5
Blad1

Since it´s an arrayformula You confirm it by using Ctrl+Shift+Enter.

HTH,
Dennis

 

[gvanbee]

Helen, the mid function is returning text (because you are extracting a number that is part of a string)
I added a *1 to the first Mid statement.


=IF(ISNUMBER(N154),N154,IF(ISNUMBER(MID(N154,LEN(N154)-1,1)*1),MID(N154,1,LEN(N154)-1),IF(ISTEXT(RIGHT(N154,2)),MID(N154,1,LEN(N154)-2),"NO")))

 

[Aladin Akyurek]

On 2002-08-28 03:46, Helen wrote:
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

If the letters (if any) are always at the end of the entries, use:

=IF(ISNUMBER(N154+0);N154+0;SUBSTITUTE(N154;SUBSTITUTE(N154;LEFT(N154;SUMPRODUCT((LEN(N154)-LEN(SUBSTITUTE(N154;{0,1,2,3,4,5,6,7,8,9},"")))));"");"")+0)

I adjusted for ";" instead of "," as list separator. Not sure whether you need to adjust also "," in {0,1,2,3,4,5,6,7,8,9}.

Aladin
This message was edited by Aladin Akyurek on 2002-08-28 05:41

 

[Helen]

A HUGE THANK YOU TO YOU ALL !!!

 

[Juan Pablo González]

I know, Helen got the answer she got, but, I just wanted to propose this one:

=MSUBSTITUTE(A1,CHAR(ROW(A1:A26)+64),"")+0

The code MSUBSTITUTE is around the board somewhere...

http://www.mrexcel.com/board/viewtopic.php?topic=18317&forum=2

This is an array formula, you need to press Control Shift Enter to use it, instead of just Enter.
_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-08-28 06:32