formula is too long

[stilgar]

I need to somehow get these 4 formula's into one cell but they are too long

=IF(AND(B4="35mm x 125mm Skirting Duct",B9="BLACK"),"PL35125DB",IF(AND(B4="35mm x 125mm Skirting Duct",B9="WHITE"),"PL35125DW",IF(AND(B4="35mm x 125mm Skirting Duct",B9="OPAL GREY"),"PL35125DO",IF(AND(B4="35mm x 125mm Skirting Duct",B9="NATURAL ANODISED"),"PL35125DN",IF(AND(B4="35mm x 125mm Skirting Duct",B9="POWDERCOATED"),"PL35125DS","")))))

=IF(AND(B4="35mm x 150mm Skirting Duct",B9="BLACK"),"PL35150DB",IF(AND(B4="35mm x 150mm Skirting Duct",B9="WHITE"),"PL35150DW",IF(AND(B4="35mm x 150mm Skirting Duct",B9="OPAL GREY"),"PL35150DO",IF(AND(B4="35mm x 150mm Skirting Duct",B9="NATURAL ANODISED"),"PL35150DN",IF(AND(B4="35mm x 150mm Skirting Duct",B9="POWDERCOATED"),"PL35150DS","")))))

=IF(AND(B4="50mm x 150mm Skirting Duct",B9="BLACK"),"PL50150DB",IF(AND(B4="50mm x 150mm Skirting Duct",B9="WHITE"),"PL50150DW",IF(AND(B4="50mm x 150mm Skirting Duct",B9="OPAL GREY"),"PL50150DO",IF(AND(B4="50mm x 150mm Skirting Duct",B9="NATURAL ANODISED"),"PL50150DN",IF(AND(B4="50mm x 150mm Skirting Duct",B9="POWDERCOATED"),"PL50150DS","")))))

=IF(AND(B4="50mm x 200mm Skirting Duct",B9="BLACK"),"PL50200DB",IF(AND(B4="50mm x 200mm Skirting Duct",B9="WHITE"),"PL50200DW",IF(AND(B4="50mm x 200mm Skirting Duct",B9="OPAL GREY"),"PL50200DO",IF(AND(B4="50mm x 200mm Skirting Duct",B9="NATURAL ANODISED"),"PL50200DN",IF(AND(B4="50mm x 200mm Skirting Duct",B9="POWDERCOATED"),"PL50200DS","")))))

Idea's?

 

[Haseeb Avarakkan]

Hello, Try this;

=IF(AND(OR(LEFT(B9)={"B","N","O","P","W"}),ISNUMBER(SEARCH("*mm*x*mm",B4))),"PL"&LEFT(B4,2)&MID(B4,8,3)&"D"&LOOKUP(LEFT(B9),{"A","B","N","O","P","W"},{"","B","N","O","S","W"}),"")

 

[stilgar]

not really sure how to use that formula. do i just copy it or do i have to enter in something?

Those formulas are specific to the part numbers aswell. i'm not sure if your one takes that in to account?

Still learning

 

[jasonb75]

How about an even shorter

=IF(COUNTA(B4,B9)=2,"PL"&LEFT(SUBSTITUTE(B4,"mm x ",),5)&"D"&SUBSTITUTE(LEFT(B9,1),"P","S"),"")

 

[Haseeb Avarakkan]

just copy & paste. You can create a validation list in B4 for '50mm x 125mm Skirting Duct'.... & B9 for 'Black', 'White'......

Does this works?

 

[Haseeb Avarakkan]

=IF(COUNTA(B4,B9)=2,"PL"&LEFT(SUBSTITUTE(B4,"mm x ",),5)&"D"&SUBSTITUTE(LEFT(B9,1),"P","S"),"")

Much much better

 

[jasonb75]

not really sure how to use that formula. do i just copy it or do i have to enter in something?

Those formulas are specific to the part numbers aswell. i'm not sure if your one takes that in to account?

Still learning

Are you saying that B4 could contain descriptions other than "Skirting Duct"?

How many descriptions / part numbers are you looking at? You will probably be better off with a lookup table.

 

[stilgar]

yeah proberly. i've got it working though just need to pretty it up

cheers guys