VBA to split text by delimiter into rows for multiple columns

[HelloItsConrad]

Hello,
I am in need for some help regarding splitting column cell values via a ";" delimiter into rows while keeping other column values blank. This will apply for all columns that contain ";".
Excel file consists of 500 rows with varying number of columns (ideally the VBA will take into account the total range of data and manipulate all columns it applies to).
Have attached an example of before and after data.
Thank you so much!

Sample data (before):

Sample data (after):

 

[James006]

Hello,

Have you tried the Text to Columns :

VBA Text to Columns

This tutorial will show you how to convert string of text in a single cell to multiple columns using the Range TextToColumns method in VBA Text to Columns

www.automateexcel.com

 

[HelloItsConrad]

Hi James,

I would like the data in the columns to be split by delimiters into the new rows below the existing row without copying non-split information in other columns.
I don't think the text to columns function works for this instance.
Thank you!

Hello,

Have you tried the Text to Columns :

VBA Text to Columns

This tutorial will show you how to convert string of text in a single cell to multiple columns using the Range TextToColumns method in VBA Text to Columns

www.automateexcel.com

 

[pjmorris]

You haven't fully defined what you want to happen. The first example in Row 2 has different data against Header's 5 and 7. Thus if you have Header 5='aaa;bbb' and Header7='ccc;ddd' what output do you want? The correct answer with multiple columns would be this:

aaa - ccc;ddd
bbb - ccc;ddd

but I suspect you want:

aaa
bbb
ccc
ddd

To achieve that you need to combine (simple concatenate) the headers with the data that needs to be split. For me, the easiest way to do this is to use PowerQuery where you import a table, combine the columns and then split them into rows (thats on the Advance bit of 'Split by Delimiter). However, it will replicate all the associated data, which from a data point of view means each row is consistent. If you then want to present it as a table without duplicated fields I would use Powerquery to create a pivottable. By default fields don't have duplicated data displayed.

HTH, though it may not be exactly what you're looking for (apologies).