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

This would probably be better implemented as a custom VBA function or using
a lookup table.
Create a table using the last 3 characters of the part numbers (although it
looks like in some places you're using only 2) and use vlookup against that
table.

Tim



"beechum1" wrote in
message ...

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

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

kassie.... i'll check out the vlookup command.... I of course, am jus
getting started. as far as the 031 thing.... i have the IC031
ID031... or will have had... if i were to complete proofing th
code.... all of them in alphabetical order, so that IF ic031 wasn'
found, and IF ID031 wasn't found, all the way to the XD031, then al
the remaining 031's will return the proper code... so perhaps i didn'
proof it, but it seems to me that the way i would have finalized it
would have worked. the capitolization also is just not having bee
proofed... once i found out that i couldn't use the line as is,
didn't proof it

--
beechum
-----------------------------------------------------------------------
beechum1's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=50142

i'm checking this vlookup out... it doens't look like it's what i need
becuase i'm not going to have the "part numbers" and the "part codes
all in the same row. the part codes won't be on this worksheet.

i've attached a sample of what i need to have happen

+-------------------------------------------------------------------
|Filename: ProMan1.zip
|Download: http://www.excelforum.com/attachment.php?postid=4222
+-------------------------------------------------------------------

--
beechum
-----------------------------------------------------------------------
beechum1's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=50142

Hi beechum

OK, I was not looking for an argument, I was just pointing out
inconsistencies. If you really want to use an IF formula, there is only one
way of doing it in a situation like this one , and that is by using a helper
column, and then splitting the IF statement over these columns. In the first
column, you will evaluate say 6 IF statements, giving a value when true. For
the False part, give a unique number, letter or something. I like using "NOT
DONE". In the second column, you start your IF statement with
=IF(B1<"NOT DONE",B1,IF( and add the rest of the formula to evaluate the
remaining possibilities. In this way, you will either get a code, or the
words NOT DONE in the first column. In the 2nd column you will carry forward
the codes from the previous column, and also evaluate the NOT DONE ones, so
that you will have only codes in this column. Hide the first column, as you
do not need to see it, and voila.

Hth



"beechum1" wrote:


kassie.... i'll check out the vlookup command.... I of course, am just
getting started. as far as the 031 thing.... i have the IC031,
ID031... or will have had... if i were to complete proofing the
code.... all of them in alphabetical order, so that IF ic031 wasn't
found, and IF ID031 wasn't found, all the way to the XD031, then all
the remaining 031's will return the proper code... so perhaps i didn't
proof it, but it seems to me that the way i would have finalized it,
would have worked. the capitolization also is just not having been
proofed... once i found out that i couldn't use the line as is, i
didn't proof it.


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

ok.... i like that idea... seeings how i don't know how to do much else,
that makes alot more sense than trying to learn something i really don't
need.

and i wasn't fightin... just saying..... I planned on proofing it as i
went, but when i found it didn't work, i didn't proof it anymore....
thanks though, i know what your saying......

So 7 level IF statements is the max then??? I guess I'll find out here
in a minute hahaha.


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

Hi Beechum

Remember, the last condition you should evaluate, is the RIGHT(B1,3) ones.
Also, make use of the IF(OR( formula to group your results. Iow, all
numbers evaluating to say "l", can be grouped togeterwith the OR bit, to
reduce the number of IF's

"beechum1" wrote:


ok.... i like that idea... seeings how i don't know how to do much else,
that makes alot more sense than trying to learn something i really don't
need.

and i wasn't fightin... just saying..... I planned on proofing it as i
went, but when i found it didn't work, i didn't proof it anymore....
thanks though, i know what your saying......

So 7 level IF statements is the max then??? I guess I'll find out here
in a minute hahaha.


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

PERFECT.... thanks alot.... the OR will work perfect for what i'm
doing... thanks


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