I have an Excel sheet with a column named, “additional_attributes”. All of the attributes are stored in the cell separated by commas. (see example value below)

I need to split each parameter/value into separate columns using the parameter name as the column heading.

Unfortunately, not all parameters exist in each cell value. Splitting the cell into columns using only the comma as a separator results in mismatched data.

How can I split this cell into separate columns with only the relevant values in the correct columns?

brand=grimble,color=Whites/Creams,cost=1.290000,fabric=Cushion,fabric_reservation=[],fire_retardant= ,free_sample=No,halfqty=No,icon_contract=No,icon_contract_curtains=No,icon_contract_upholstery=No,icon_craft=No,icon_curtains=No,icon_lightuse=No,icon_roman_blinds=No,icon_upholstery=Yes,is_recurring=No,location=CR1,made_to_measure=No,made_to_measure_weeks=4 ( up to 6 weeks during high demand periods),must_ship_freight=0,price_units=Unit,related_categories=Trimmings|Linings / Tapes|Cushions,repeat=nil,rrp=6.950000,shipperhq_shipping_group=UK Only,shipping_location=UK Only,ship_separately=0,special_shipping_group=None,sub_tite=Polyester,suitability=upholstery,Supplier=Hallis Hudson,supplier_product_code=CPHF18,url_path=18-polyester-cushion-pad-insert,width=NA

CodePudding user response：

I would add the whole set of parameters as column headers. Then you can use the following formula (Excel 365):

=LET(Parameter,C$3,
startValue,FIND(Parameter,[@[additional_attributes]])   LEN(Parameter)   1,
nextComma,IFERROR(FIND(",",[@[additional_attributes]],startValue),LEN([@[additional_attributes]]) 1),
IFERROR(MID([@[additional_attributes]],startValue,nextComma-startValue),"-"))

xyz is no parameter, therfore result is "-"