Split cell and name columns

[megadentalda]

Hello,
Firstly hope everyone is staying sfae.

I have a list of excel rows with cells like this:

Product ID: 3943, Product Qty: 1, Product SKU: LPFL-B, Product Name: Gloves Latex Powder Free Size Large (BLOSSOM), Product Weight: 0.0000, Product Variation Details: , Product Unit Price: 14.95, Product Total Price: 14.95|Product ID: 12491, Product Qty: 1, Product SKU: Opwil1, Product Name: Optim 33 Disinfectant Wipes 160/Can, Product Weight: 1.0000, Product Variation Details: , Product Unit Price: 29.95, Product Total Price: 29.95

to be clear the above is all in one cell.

I need to create columns with headers "Product ID", "Product Qty", "Product Name" etc with the content of cell being "3943", "LPFL-B", "Gloves Latex Powder Free Size Large (BLOSSOM)" etc. but also if there is a "|"will need to insert a line and copy everything down 1 cell and then separate into columns as above....

any help with this will be very appreciative.

thanks

 

[jimrward]

A few questions to begin with
how did you get this data in to excel
what was the original source of this data
is this a one off exercise or going to be repeated
is it always the same number of columns or is there instances of missing data, if so is it blank or does it still contain the header but no data

 

[alansidman]

Is this what you are looking for:

Book1
	A	B	C	D	E	F	G	H
1	Product ID	Product Qty	Product SKU	Product Name	Product Weight	Product Variation Details	Product Unit Price	Product Total Price
2	3943
3		1
4			LPFL-B
5				Gloves Latex Powder Free Size Large (BLOSSOM)
6					0
7
8							14.95
9								14.95|Product ID
10		1
11			Opwil1
12				Optim 33 Disinfectant Wipes 160/Can
13					1
14
15							29.95
16								29.95
Sheet2

Created using Power Query.

Mcode:  
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.3"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1.1]), "Column1.1", "Column1.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 

[megadentalda]

Is this what you are looking for:

Book1
	A	B	C	D	E	F	G	H
1	Product ID	Product Qty	Product SKU	Product Name	Product Weight	Product Variation Details	Product Unit Price	Product Total Price
2	3943
3		1
4			LPFL-B
5				Gloves Latex Powder Free Size Large (BLOSSOM)
6					0
7
8							14.95
9								14.95|Product ID
10		1
11			Opwil1
12				Optim 33 Disinfectant Wipes 160/Can
13					1
14
15							29.95
16								29.95
Sheet2

Created using Power Query.

Mcode: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.3"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1.1]), "Column1.1", "Column1.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

almost there. need everything in 1 row. until we see the "|" which then brings everything after in next line. b/c its next SKU. i notice for 2nd SKU there is nothing in 1st column and also in last column the word "product ID" still showed up in the cell....

would love a vba code for this because client does not have latest version of excel so do not believe they ave access to power query

 

[megadentalda]

A few questions to begin with
how did you get this data in to excel
what was the original source of this data
is this a one off exercise or going to be repeated
is it always the same number of columns or is there instances of missing data, if so is it blank or does it still contain the header but no data

1) it is an export for a ecommerce website
2) no it will be constantly repeated which is why i am looking for a vba code, if one off would just spend the manula time of doing it myself...
3) always same number columns will have data header but no data afetr it.. just a ","

thanks

 

[megadentalda]

hello

thanks for the help... but i have ended up finding a seperate way of exporting the orders which gets this done for me automatically.

thank you for the offer to help!!

 

[alansidman]

client does not have latest version of excel so do not believe they ave access to power query

FYI: PQ has been around since Excel Version 2010. So at least 10 years.