Convert text to row for records in columns highlighted

[satya04]

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.

[gawa]

Hi @satya04 

It is very easy by using Text to Column. Text to Column has an option to split records into row direction, so it is the best fit to your case.

Refer to the attached WF for your ready reference.

[Peachyco]

I'd just like to warn about chaining Text To Column tools in this way to break apart multiple columns into rows: For each column that you break down, your dataset is likely to grow exponentially.

Consider this example:

You have to be sure that this is the behavior/outcome that you want.

[Christina_H]

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.

[satya04]

Can you please share the working file?

[Christina_H]

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