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

#### TheTallBloke

##### New Member
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.

Example below of before/after:

 order number product id product name product price product quantity order total etc 1​ 1 a 100.00 50.00 5000​ abc 2​ 2:3 b:c 100.00:26.50 4.00:8.50 625.25​ def 3​ 4:5:6 d:e:f 200.50:45.50:72.00 1.00:1.00:2.50 426​ ghi

 order number product id product name product price product quantity order total etc 1​ 1 a 100.00 50.00 5000​ abc 2​ 2 b 100.00 4.00 625.25​ def 2​ 3 c 26.50 8.50 625.25​ def 3​ 4 d 200.50 1.00 426​ ghi 3​ 5 e 45.50 1.00 426​ ghi 3​ 6 f 72.00 2.50 426​ ghi

### Excel Facts

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
Hi,​
where should be the expected result, in same worksheet ?​

#### Marc L

##### Well-known Member
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
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

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

#### TheTallBloke

##### New Member
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
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 …​

Replies
0
Views
79
Replies
0
Views
322
Replies
13
Views
8K
Replies
5
Views
341
Replies
1
Views
297

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.

### Which adblocker are you using?

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

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