Excel Pro Plus 2019 read folder names & extract parts of name to specific columns?

[Event2020]

Windows 10
Excel Professional Plus 2019

I have a large number of folders which I want excel to scan and create a list of the folder names but it is not simple as the folders are named in a particular way and excel needs to extract different parts of the folder name and place it in separate columns.

Example - each folder is named in the same way...

Code 18_-_Oranges_&_Apples_[Fruit]_(20_Packs)_9.5_kilos_10.Boxes_Yes
Code 34_-_Raisins_[Dried_Fruit]_(8_Packs)_50_grams_4.Boxes_No

In Excel the above example should become

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7
Code 18	Oranges_&_Apples	Fruit	20 Packs	9.5 kilos	10.Boxes	Yes
Code 34	Raisins	Dried Fruit	8 Packs	50 grams	4.Boxes	No

This is beyond my skill so any help would be very much appreciated.

 

[mikerickson]

I'm looking at the result Oranges_&_Apples and the result 20 Packs. Is it correct that column 2 preserves the in-string underscores, while column 4 and 5 should change them to spaces?

 

[Event2020]

Hi mikericksom

woops, that was a mistake on my part.

All in-string underscores should be changed to spaces or ignored, as should [ ] in column 3 and ( ) in column 4.

I would edit my original post to reflect this but I am unable to discover an edit function for my post.

 

[Event2020]

Any of you kind people have any suggestions?