Greetings thy brave warriors of numbers and spread sheets!
I come here humbly asking for help and enlightenment. It's all quite complicated, but I will try to explain it as simply as I can.
Intro:
I'm currently working for a pet food franchise helping them organize their back end for their web shop. This means that every dog food/cat food product is organized in excel and then later pushed to their web shop system called "Mediablob". It's a lot of manual labor and at this pace we won't finish in time for the deadline in 10 days.
So the workflow is Excel -> Mediablob -> Website. If I solve the Excel issue everything else will work too.
Problem:
Among all the columns (Brand, Size, Color, Description) the one that I need help with is called "Text 12".
Text 12 = Nutrition facts
This info is copied from the pet food brands such as this (under the tab "innehåll") and the format, when copied, looks like this:
Protein (%) 24,0
Fett (%) 18,0
Aska (%) 5,2
Stärkelse (%) 36,4
Växttråd (%) 1,6
Vattenhalt (%) 9,5
Kostfiber (%) 6,9
Linolsyra (%) 3,65
Arakidonsyra (%) 0,07
Omega 6 (%) 3,84
Omega 3 (%) 0,65
EPA/DHA (%) 0,25
Kvävefria extraktivämnen (NFE) (%) 41,7
Omsättbar energi (kalkylerad enligt NRC85) (kcal/kg) 3.829,5
Omsättbar energi (uppmätt) (kcal/kg) 4.078,0
Kalcium (%) 0,8
Fosfor (%) 0,6
Magnesium (%) 0,12
If there is any way I could take that, and switch the %-character (and the kcal/kg) with the numbers, as well as make it all be in one row of text, that, my friends, would make 3 weeks of work into a mere hour of formula lovin'. The end result would have to look like this:
Protein 24,0% Fett 18,0% Aska 5,2% Stärkelse 36,4% Växttråd 1,6% Vattenhalt 9,5% Kostfiber 6,9% Linolsyra 3,65% Arakidonsyra 0,07% Omega 6 3,84% Omega 3 0,65% EPA/DHA 0,25% Kvävefria extraktivämnen (NFE) 41,7% Omsättbar energi (kalkylerad enligt NRC85) 3.829,5kcal/kg Omsättbar energi (uppmätt) 4.078,0kcal/kg Kalcium 0,8% Fosfor 0,6% Magnesium 0,12%
From here, I've figured out a 6 step process with the "replace" function to make something like this:
Protein 21%, fett 10%, kolhydrater (NFE) 50,5%, växttråd 2,5%, mineraler (aska) 6% (kalcium 1,2%, fosfor 1,0%), vatten 10%. Omsättbar energi 1400 kJ/100g.
Into something like this:
-end result-
”Protein”,”21”,”%”;”fett”,”10”,”%”;”kolhydrater”,”(NFE)”,”50,5”,”%”;”växttråd”,”2,5”,”%”;”mineraler”,”(aska)”,”6%”;”(kalcium”,”1,2”,”%”;”fosfor”,”1,0”,”%)”;”vatten”,”10”,”%”;”Omsättbar”,”energi”,”1400 kJ/100g";
-end result-
If there's any way to get to the end result (above) directly (where [space] is replaced by [","] and each item is seperated by [";"] then,
that would be excel-lent, but I imagine that to be fusion-science-leveled black-hole-creating answer-to-the-meaning-of-life mind boggling.
hope any of this makes sense, and I am forever grateful for anyone who's up to the challenge of spreading some excel-lence up in this sheet.
Best, Simon
Bookmarks