# formula is too long

#### stilgar

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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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"}),"")

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

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

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

Does this works?

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

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.

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

cheers guys

Replies
2
Views
768

1,216,515
Messages
6,131,110
Members
449,620
Latest member
MatXm

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

### Which adblocker are you using?

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