Convert concatenated cell into multiple rows, whilst repeating non-concatenated adjacent cells

TheTallBloke

New Member
Joined
Sep 3, 2015
Messages
25
Hi all,

I've got a massive sales order report where all multiple row data (products, quantity, prices) belonging to the same order have been concatenated in a single cell (rather than a nice, structured 1/row that I could pivot easily).

Can you please help with converting all the concatenated data (always delimited by the colon symbol) breaks into separate rows, whilst the non-concatenated data gets repeated against them?
If possible I would like to be able to specify the columns where concatenated data is expected, to make this scalable.

Many thanks in advance!


Example below of before/after:



order numberproduct idproduct nameproduct priceproduct quantityorder totaletc
1​
1a100.0050.00
5000​
abc
2​
2:3b:c100.00:26.504.00:8.50
625.25​
def
3​
4:5:6d:e:f200.50:45.50:72.001.00:1.00:2.50
426​
ghi


order numberproduct idproduct nameproduct priceproduct quantityorder totaletc
1​
1a100.0050.00
5000​
abc
2​
2b100.004.00
625.25​
def
2​
3c26.508.50
625.25​
def
3​
4d200.501.00
426​
ghi
3​
5e45.501.00
426​
ghi
3​
6f72.002.50
426​
ghi
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
where should be the expected result, in same worksheet ?​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
A demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
  Const D = ":"
    Dim R&, N&, Rg As Range
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Rows
        For R = .Count To 2 Step -1
               N = UBound(Split(.Cells(R, 2).Text, D))
            If N Then
                   .Item(R).Copy:  .Item(R + 1).Resize(N).Insert xlDown
                For Each Rg In .Item(R).Resize(N + 1).Columns
                    If InStr(Rg.Cells(1).Text, D) Then Rg.Value2 = Application.Transpose(Split(Rg.Cells(1).Text, D))
                Next
            End If
        Next
    End With
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
End Sub
 
Last edited:

TheTallBloke

New Member
Joined
Sep 3, 2015
Messages
25
Sorry about the delay, crazy last few days and I missed the notification completely.

New worksheet or same worksheet - whatever is easier.

I'm a bit confused about how to run the above - I've added it as a module and tried to run it from the VBA window but nothing seems to be happening? Thanks in advance.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Well works on my side according to your initial post sample data from the active sheet to the same active sheet …​
 

TheTallBloke

New Member
Joined
Sep 3, 2015
Messages
25
Sorry, I may be too much of a newbie to VBA to know how to run it properly, would you be able to guide me please?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
Just paste the source data to an Excel empty worksheet then paste the code in a standard module or in the worksheet module whatever​
then you can execute the demonstration from the worksheet via the Developer tab or via the key combo Alt + F8 …​
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

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
Top