CSV Sorting

Agnarr

New Member
Joined
Jan 15, 2023
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello everyone.
I have a table exported from my site but unfortunately the exported features section arrives as a single column.
My problem is that all features are in there and many products don't share same features so when i ask excel to divide the column into many the data don't match.
I mean that in the first column i may have Brand, Size and Length in the next column may be Size and Orientation etc.
Can someone help me in sorting all this mess out please?
Underneath is a small example:
OriginXML: S[Base Import 1]; Κατασκευαστής: E[Nobel Sport Italia]; Μέγεθος: S[No10]; Διαμέτρημα: S[.12]; Βάρος Σκαγιών: S[32gr]; GROUP ID: T[0017]; (Skroutz Options) Απόκρυψη από Google / Meta: C[Y]
Κατασκευαστής: E[Nobel Sport Italia]; Μέγεθος: S[No8]; Βάρος Σκαγιών: S[28gr]; Επιπλέον Πληροφορίες: T[Sporting ]
OriginXML: S[Base Import 1]; Κατασκευαστής: E[Nobel Sport Italia]; Διαμέτρημα: S[.12]; GROUP ID: T[0119]
OriginXML: S[Base Import 1]; Κατασκευαστής: E[FIOCCHI AMMUNITION]; Βάρος Σκαγιών: S[24gr]; GROUP ID: T[86198705]; (Skroutz Options) Απόκρυψη από το Skroutz.gr: C[Y]; Επιπλέον Πληροφορίες: T[Trap]; (Skroutz Options) Απόκρυψη από Google / Meta: C[Y]
Κατασκευαστής: E[FIOCCHI AMMUNITION]; Βάρος Σκαγιών: S[24gr]; Επιπλέον Πληροφορίες: T[Trap]
Κατασκευαστής: E[RC Cartridges]; Βάρος Σκαγιών: S[24gr]; Επιπλέον Πληροφορίες: T[Trap]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hello everyone.
I have a table exported from my site but unfortunately the exported features section arrives as a single column.
My problem is that all features are in there and many products don't share same features so when i ask excel to divide the column into many the data don't match.
I mean that in the first column i may have Brand, Size and Length in the next column may be Size and Orientation etc.
Can someone help me in sorting all this mess out please?
Underneath is a small example:
OriginXML: S[Base Import 1]; Κατασκευαστής: E[Nobel Sport Italia]; Μέγεθος: S[No10]; Διαμέτρημα: S[.12]; Βάρος Σκαγιών: S[32gr]; GROUP ID: T[0017]; (Skroutz Options) Απόκρυψη από Google / Meta: C[Y]
Κατασκευαστής: E[Nobel Sport Italia]; Μέγεθος: S[No8]; Βάρος Σκαγιών: S[28gr]; Επιπλέον Πληροφορίες: T[Sporting ]
OriginXML: S[Base Import 1]; Κατασκευαστής: E[Nobel Sport Italia]; Διαμέτρημα: S[.12]; GROUP ID: T[0119]
OriginXML: S[Base Import 1]; Κατασκευαστής: E[FIOCCHI AMMUNITION]; Βάρος Σκαγιών: S[24gr]; GROUP ID: T[86198705]; (Skroutz Options) Απόκρυψη από το Skroutz.gr: C[Y]; Επιπλέον Πληροφορίες: T[Trap]; (Skroutz Options) Απόκρυψη από Google / Meta: C[Y]
Κατασκευαστής: E[FIOCCHI AMMUNITION]; Βάρος Σκαγιών: S[24gr]; Επιπλέον Πληροφορίες: T[Trap]
Κατασκευαστής: E[RC Cartridges]; Βάρος Σκαγιών: S[24gr]; Επιπλέον Πληροφορίες: T[Trap]
Hi @Agnarr,

Not sure to fully understand your needs about the required columns. Suggest you share your expected result. A proposal to split columns.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    fx = (x) => let y = List.Zip(List.Transform(Text.Split (x, ";") , each Text.Split(_,":"))) in
                Table.FromRecords({Record.FromList(y{1}, List.Transform(y{0}, Text.Trim))}),
    Result = Table.Combine(Table.TransformColumns(Source, {"Column1", each fx(_)})[Column1])
in
    Result

Regards.
 
Upvote 1

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top