Convert text to row for records in columns highlighted

Question

Hi,

I would like to convert text to rows for certain records which are currently separated by semicolon within a column. There could be mutliple records separated by semicolon within a particular record.

Please refer to columns AE, AF, AG, AY, AZ and BA in the attached file.

These records for columns within industry, sector, and subsector should be converted into separate rows using a text to row approach. Ideally they should have a record ID to indicate that they belong to a specific record. All records are tagged to a unique ID stated in col A.

Please guide me on how the workflow can be prepared for this case.

Test.xlsx

Christina_H · Answer

For mine?  See attached.  Like I said it's not ideal, far too much checking and unchecking of columns for my liking!

Multi column split.yxmd

satya04 · Answer

Can you please share the working file?

Christina_H · Answer

The best I was able to come up with in this case was transposing the data before using text to columns, then numbering the outcome and cross-tab back into columns.  Not necessarily ideal but it could work.