Text to row for large dataset with multiple dependent columns

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.

Points to note

* Please refer to columns AE, AF, AG, AY, AZ and BA in the attached file which are to be converted. 
* Additional columns for Country name, SIC code, NAIC code are also dependent on the data and needs to be adjusted.

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.

Issue

There are over 3 million rows and this is not getting added to the output file because of this reason and the process is being aborted.

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

Test.xlsx

binu_acs · Answer

@satya04 one way of doing this with the batch macro

Split_210923.yxzp

Prometheus · Answer

@satya04 There's probably an easier way to do this. It's not super scalable if you want to add more fields to text to rows, but I think this may work for you with the fields you identified.

Text to Rows.yxzp