Hi beechum1

To start with, the limitation is not the number of characters per cell, but
that an IF statement allows 7 levels, whereas you use anything from 9 to 14
levels, depending on how you look at it!

Then again, confusion reigns here at present! You state
that i have to be able to use the last 3 characters to know what part.....

In fact, you use either 1, 2, 3 or 5 characters as criteria.
Also, you use the same "last 3 digits" in a number of ways. 031 is used as
such:
031; ib031; id031; mb031; me031; xc031; xd031; yb031 and yc031, each with a
different result. Your statement is therefore totally wrong.

This makes it extremely difficult to write a formula to evaluate any given
number.
You make use of both upper and lower case characters as a result. Is this a
typo, or intentional? Ideally one would use VLOOKUP to obtain a result, but
that would mean that you will have to incorporate your codes in your
database, since I cannot see you writing a vlookup given the inconsistencies.
For example = VLOOKUP(Right(B1,3),Parts,5,FALSE) or something to that
effect. You however have to test B1-(5 digits), RIGHT(B1,1), RIGHT(B1,2) and
RIGHT(B1,3).

I would therefore suggest that you add a column to the parts database, and
give each part its own code, based on your criteria. You do this once, and
thereafter use a simple VLOOKUP formula to retrieve the code when you need
to, same as you would retrieve the Description, Unit Cost, Selling price etc
--
ve_2nd_at. Stilfontein, Northwest, South Africa


"beechum1" wrote:


so here's the function.....

=if(RIGHT(B1,2)="11","J",if(RIGHT(B1,2)="12","J",i f(RIGHT(B1,)="13","J",
if(b1=id021,"a",if(b1=xd021,"A",if(RIGHT(B1,2)="20 ","b",,if(RIGHT(B1,2)=
"21","B",",if(RIGHT(B1,2)="22","B",if(b1="ib031"," D",if(b1="id054,"D",if(b1=
"me031","d",if(b1="mb031,"D",if(b1="yb031,"D",if(b 1="yc031,"D",if(b1="xd031,
"D",if(b1="xc031,"D",if(RIGHT(B1,3)="031","E",if(R IGHT(B1,3)="032","E",
if(RIGHT(B1,3)="041","E",if(RIGHT(B1,3)="042","E", if(RIGHT(B1,3)="051",
"C",if(b1=db052,"g",if(RIGHT(B1,3)="053","c",if(RI GHT(B1,3)="054","D",if
(b1=db055,"g",if(RIGHT(B1,3)="061","E",if(RIGHT(B1 ,3)="062","E",if(RIGHT
(B1,3)="071","E",if(RIGHT(B1,3)="072","e",if(RIGHT (B1,3)="080","I",if(RIGHT
(B1,3)="081","I",if(RIGHT(B1,3)="082","I",if(RIGHT (B1,3)="083","I",if(RIGHT
(B1,3)="084","I",if(RIGHT(B1,3)="085","I",if(RIGHT (B1,3)="086","I",if(RIGHT
(B1,3)="087","I",if(RIGHT(B1,3)="088",if(b1=db122" ,"B",if(RIGHT(B1,3)="121",
"a",if(RIGHT(B1,3)="123","A",if(RIGHT(B1,3)="221", "B",if(RIGHT(B1,1)="l","N",
if(RIGHT(B1,1)="r","n",if(b1="hd981","I",if(b1="id 981","I",if(RIGHT(B1,3)=
"081","G"))))))))))))))))))))))))))))))))))))))))) ))))))

the value in B1, will be any of the following and more.....
HB011,HB021,HB031
HB041,HB051 something like that..... we have about 300 part numbers
that i have to be able to use the last 3 characters to know what part
CODES they are. the first two letters indicate the year and model, and
the last three numbers indicate which part, and for what application. it
appears however that a cell can only hold 255 charachters or so and i
can't fit my function in there.

if not the function, or some code, how can i get all this done?


--
beechum1
------------------------------------------------------------------------
beechum1's Profile: http://www.excelforum.com/member.php...o&userid=30489
View this thread: http://www.excelforum.com/showthread...hreadid=501427