Hi All
I am currently migrating 30,000 product SKU's and descriptions from our website into our stock control system. The data needs to go into the stock system in a slightly different format to the website and i am trying to find a formula that would save me at least a few hours
in the attached file Column A is the Product SKU and column B is the product description.
Each product is made up of 2 parts. In this instance the product is a chair and it is made up of a chair top and a chair base. So in the file attached A1 is the chair as a whole, A2 is the chair top and A3 is the chair base. We do this because the parts are stocked independently from each other and we need to show this in the stock control software.
Where I am getting stuck is the product description at the moment each 3 lines have the same description - which is the chair as a whole but I want to do is have the chair as a whole description in B1, the chair top description in B2 and the chair base description in B3
What I am trying to achieve in column A and C for the first product is
Column A
DF0106-BS-BASE-MAPLE-BLACK-
DF0106-TOP-FG514-G+
DF0106-BS-BASE-MAPLE-BLACK-
Column B
Charles Eames DSW Barstool Black Gloss Fibreglass Maple Powder Coated Black
Charles Eames DSW Black Gloss Fibreglass Top
Charles Eames DSW Maple Powder Coated Black Base
Now I have been able to achieve this by using these 2 formulas
Formula in C2
="Charles Eames DSW "&MID(B1;(FIND(" ";B1;LEN("Charles Eames DSW ")+1)+1);FIND("Maple
";B1)-(FIND(" ";B1;LEN("Charles Eames DSW ")+1)+1))&"Top"
Formula in C3
="Charles Eames DSW "&RIGHT(B1;LEN(B1) - FIND("Fibreglass";B1) -9) & "Base"
However, I have a couple of issues. Firstly the chair base can have either 'Maple' , 'Oak', 'Walnut', 'Oak Painted Black' or 'Oak Painted White' wood in the description and the formula for C2 at the moment only works with the word 'Maple' in the product description. So I am trying to find a way for the FIND rule to search for 1 of 5 words. Secondly the chair tops are made from either 'Fibreglass' or 'Plastic' so again the formula in C3 would only work if the product description had the word 'Fibreglass' in it and wanted to know if there again was a FIND formula that could find 1 of 2 words for this formula
If anyone could help with this I could then pull the formula down the 30,0000 rows and it would change them all automatically.
If you need more information please let me know.
Thank you for your help
James
Bookmarks