Using Macros to Automatically Insert New Rows to Separate Cells with Delimiter

[student93]

Hello everybody. This is my first time posting on this forum so please be patient with me.

So I managed use infopath to collect data and exported to an excel file. The problem is that in infopath, all fields that are from a repeating table are combined into one cell separated by a semicolon. Therefore I need to find a macro that will be able to do the following:
1. check each cell in a row to find the cell with the most number of semicolons (this will tell the macro to insert this many rows)
2. push back the unexpanded data behind to make room to expand the cells from the first row and convert a list of semicolons into individual rows
3. go to the next unexpanded column and do the same until the end of the database
4. if possible, fill in the gaps in the A column with the corresponding number

I have multiple repeating tables so I was wondering if there is a macro that can check if there are extra semicolon on each cell in each column and add rows accordingly.

I been looking online for a solution that can do this, but I couldn't find it. I'm hoping that there is a universal macro that can accomplish all of the above so not only I can use it for this particular database, but other people who are exporting infopath with repeating tables to excel can also use this macro.

Below is an example with the excel file. Obviously that is not the database I have. (The database I have is far longer and has at least 6 repeating tables)

Any advice or pointers would be wonderful
Thank you so much


After.PNGBefore.PNG