Formula problem

Helen

Board Regular
Joined
Feb 19, 2002
Messages
103
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
Blad1


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

HTH,
Dennis
 
Upvote 0
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")))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top