Separate items in one cell to multiple rows

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
196
Office Version
  1. 365
Platform
  1. Windows
I have a list of machine models in one cell on one row.
I want to separate the machine models that are listed together in one cell separated by a comma and put each machine model in it's own row.

How do I do this in Excel or in Visual Basic?

Thanks for your amazing help.

You guys are great.

Matt
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
An easy way to do this in Excel would be to use Text-To-Columns, use comma as the delimiter. Then copy>paste special(transpose) the result.

For VBA:

Code:
Public Sub mljohn()
Dim ModelArray  As Variant, _
    i           As Long
ModelArray = Split(Range("A1").Value, ",")
Application.ScreenUpdating = False
For i = LBound(ModelArray) To UBound(ModelArray)
    Range("A" & i + 1).Value = ModelArray(i)
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Wonderful.

Another question.

Each machine model has other column fields that I want to duplicate on each row of the transposed machine models.

How do I do that?

Matt
 
Upvote 0
Can you please provide an example as to how your data is laid out?
 
Upvote 0
I wish I could copy and paste but I don't know how to do that.

Vendor | Category| Printer | Compatible with | OemPartNo |MSEPartNo | PremiumPartNo | PageYield | Price

Here are the fields. Did you want something else?
 
Upvote 0
Compatible with field is where the Machine models are.
 
Upvote 0
So is this a separate table that has each of the machine models in it, and you are wanting to lookup certain values?
 
Upvote 0
I have a row that has this in the compatible with field:
DCP-1200, 1400, Fax-4750, 5750, 8350p, 8750p, HL 1030, 1230, 1240, 1250, 1270n, 1435, 1440, 1470n

If I put "DCP-1200" in it's own row, I want all the info that was in all the other fields to be duplicated for each machine that was in that one cell before they were separated.

As a partial example it would look like this but would include the rest of the fields.

MSE TONER BROTHER DCP-1200 DR400
MSE TONER BROTHER DCP-1400 DR400
MSE TONER BROTHER Fax-4750 DR400
MSE TONER BROTHER HL 1030 DR400
 
Upvote 0
How do I automate this and also get the other fields to duplicate when the machines in the "compatible with" column are moved to separate rows?
 
Upvote 0

Forum statistics

Threads
1,212,148
Messages
6,106,246
Members
448,009
Latest member
Brgrant820

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