Formula problem

Helen

Board Regular
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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Helene,

Instead of revise Your formula I would like You to test following solution:
Bok1
ABCD
123465768AA23465768
2345678A345678
3123AA123
4AA123AA123
5

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

HTH,
Dennis

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")))

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}.

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

A HUGE THANK YOU TO YOU ALL !!!

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

Replies
10
Views
347
Replies
7
Views
468
Replies
1
Views
261
Replies
1
Views
316
Replies
2
Views
235

1,218,691
Messages
6,143,943
Members
450,516
Latest member
shironokuro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back