VBA to split text by delimiter into rows for multiple columns

HelloItsConrad

New Member
Joined
Feb 5, 2023
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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):
1675587778516.png


Sample data (after):
1675588002181.png
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hello,

Have you tried the Text to Columns :
 
Upvote 0
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 :
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top