Question

^{Please Solve This Excel Worksheet and List your Formulas, and How you got your answers so that I can learn from what you did. Please take your time as I know this will take a while!
K LM L B C D E F G H I J Use only a combination of text manipulation (i.e., MID, CONCATENATE, TRIM, etc.) and/or logical and conditional formulas (i.e., IF, AND, etc.) to populate the green cells below. Product Base Sales COGS Color Plus x #units Sales COGS Plus x #units Size Sales COGS 5 Product1.5000.500. Orange.5.2.XS.2.1 6 Product2.7500.1000.Blue.4.2.5.4.2 7 Product3.2500.200.Red.3.1.M.10.4 8 Product4.8000.2000.Pink.3.1.L.20.9 9 Product5.10000.1750.Grey.5.3.XL.30.16 10 Product6.1500.75. Green.3.1 11 Product7.4000.600.Yellow.7.3 12 Product8.8500,1750.Purple.9.4 13 Product9.11000.3500. Brown.8.4 14 Product10.9000.4000.White.6.3 15 Black.11.5 16 Tan.7.3 17 Teal.6.3 18 Maroon. 10.4 19 Olive.3.2}

Answer 1

Delimiter: m - Product1.5000.500.Orange.5.2.xs.2.1 Product2.7500.1000.blue.4.2.5.4.2 Product3.2500.200.red.3.1.M.10.4 Product4.8000.2000.Pink.3.1.L.20.9 Product5.10000.1750. Grey.5.3.XL.30.16 Product6.1500.75.Green.3.1 Product7.4000.600.Yellow.7.3 Product8.8500.1750.Purple.9.4 Product9.11000.3500. Brown.8.4 Product10.9000.4000.white.6.3 Black.11.5 Tan.7.3 Teal.6.3 Maroon. 10.4 Olive.3.2 Base Plus x * #units Plus x * #units Product Sales COGS Color Sales COGS Size Sales COGS Producti 5000 500 Orange 5 2 x 2 1 Product2 | 7500 1000 blue 4 2 | 5 | 4 2 Product3 | 2500 200 red 3 1 M 10 4 Product4 | 8000 2000 Pink 3 1 20 9 Product5 10000 1750 Grey 5 3 XL 3016 Producto 1500 75 Green 3 Product7 4000 600 Yellow 7 Products 8500 1750 Purple 9 Product9 11000 3500 Brown | 8 Product10 9000 4000 white 6 Black 11 Tan 7 Teal | 6 | Maroon 10 Olive 3 2

Product Sales COGS Color Sales COGS Size Sales COGS IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6)), FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN(B6:$B6)) +COLUMN(B6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN(SB6:C6))+COLUMN(C6)), FIND($B$3,RIGHT($A6, LEN(SA6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:06))+COLUMN(D6)),FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:06))+COLUMN(D6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:E6)) +COLUMN(E6)), FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:56)) +COLUMN(E6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:F6)) +COLUMN(F6)), FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6)), FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3, $A6, IF(COLUMN()2,1,SUMPRODUCT(LEN ($B6:G6))+COLUMN(G6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:H6)) +COLUMN(H6)), FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6))-1),0))&$B$3)-1),"") IFERROR(MID($A6,IF(COLUMN()2,1,SUMPRODUCT(LEN($B6:16))+COLUMN(16)),FIND($B$3,RIGHT($A6, LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN(2,1,SUMPRODUCT(LEN($B6:16))+COLUMN(16))-1),0))&$B$3)-1),"")

In order for above formulas to work, paste the raw data in Cell A6 onwards, and enter a "." in cell B3, which will act as a Delimiter.