Separating list of text into multiple cells.rows

T

Traceelewis71

New Member
Joined
Dec 5, 2017
Messages
12
I have a list of positions for employees that are in one cell that I needs to be in rows for each employee. Text to column doesn't work and neither does any sort of text formula like =left, =mid, etc. I have over 2500 employees and th
[TABLE="width: 500"]
<tbody>[TR]
[TD]Active Status[/TD]
[TD]Emp ID[/TD]
[TD]Worker[/TD]
[TD]Hire Date[/TD]
[TD]Position ID[/TD]
[TD]Position for Worker[/TD]
[/TR]
[TR]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]123456[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]John Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169, align: right"]4/4/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Job-6-2768[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 404"]
<tbody>[TR]
[TD="class: xl65, width: 404"]Bartender - John Smith (+)

Bartender Training - John Smith (+)

FOH E Learning - John Smith (+)

SA Training - John Smith (+)

Server - John Smith

Server Assistant-Tipped - John Smith (+)

Server Training - John Smith (+)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]234567[/TD]
[TD]Jane Doe[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169, align: right"]2/27/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Job-6-1033[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 404"]
<tbody>[TR]
[TD="class: xl65, width: 404"]Bartender - Jane doe (+)

Bartender Training - Jane doe (+)

FOH E Learning - Jane doe (+)

Server - Jane doe

Server Training - Jane doe (+)

Team Lead - Jane doe (+)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]345678[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Adam Eve[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169, align: right"]3/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl65, width: 169"]Job-6-1060[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 404"]
<tbody>[TR]
[TD="class: xl65, width: 404"]Bartender - Adam Eve (+)

Bartender Training - Adam Eve (+)

FOH E Learning - Adam Eve (+)

Server - Adam Eve

Server Training - Adam Eve (+)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
post a link to shared excel file with your example data. use dropbox, onedrive, googledrive or any similar
 
Upvote 0
I suppose your data on sheet1 is like this:


<b>Sheet1</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98.85px;" /><col style="width:70.34px;" /><col style="width:102.65px;" /><col style="width:90.3px;" /><col style="width:90.3px;" /><col style="width:273.74px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Active Status</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Emp ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Worker</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Hire Date</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position for Worker</td></tr><tr style="height:130px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender - John Smith (+)<br />Bartender Training - John Smith (+)<br />FOH E Learning - John Smith (+)<br />SA Training - John Smith (+)<br />Server - John Smith<br />Server Assistant-Tipped - John Smith (+)<br />Server Training - John Smith (+)</td></tr><tr style="height:111px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender - Jane doe (+)<br />Bartender Training - Jane doe (+)<br />FOH E Learning - Jane doe (+)<br />Server - Jane doe<br />Server Training - Jane doe (+)<br />Team Lead - Jane doe (+)</td></tr><tr style="height:93px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender - Adam Eve (+)<br />Bartender Training - Adam Eve (+)<br />FOH E Learning - Adam Eve (+)<br />Server - Adam Eve<br />Server Training - Adam Eve (+)</td></tr></table>

-----------

The data on sheet 2 will look like this:

<b>Sheet2</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:112.16px;" /><col style="width:47.52px;" /><col style="width:101.7px;" /><col style="width:71.29px;" /><col style="width:116.91px;" /><col style="width:362.14px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Active Status</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Emp ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Worker</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Hire Date</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position for Worker</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >FOH E Learning - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >SA Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server - John Smith</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server Assistant-Tipped - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender Training - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >FOH E Learning - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Server - Jane doe</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Server Training - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Team Lead - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender Training - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >FOH E Learning - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Server - Adam Eve</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Server Training - Adam Eve (+)</td></tr></table>

------
Use this macro:

Code: 
Sub Separating_list()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, p As Variant, n As Long
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Cells.ClearContents
    sh1.Rows(1).Copy sh2.Rows(1)
    n = 2
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        For Each p In Split(c.Offset(, 5), Chr(10))
            sh2.Cells(n, "A").Resize(1, 5).Value = c.Resize(1, 5).Value
            sh2.Cells(n, "F").Value = p
            n = n + 1
        Next
    Next
    MsgBox "Done"
End Sub

------------

INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.
Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select Separating_listand press Run.
 
Upvote 0
Thank you both!! I was trying to rewrite the report with a calculation to separate the field in the report in our system.

Dante!! This worked Thank you!!
 
Upvote 0
Traceelewis71 said:
Thank you both!! I was trying to rewrite the report with a calculation to separate the field in the report in our system.

Dante!! This worked Thank you!!
Click to expand...


I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
IF, OR, Index, Match, Multiple Criteria
2
Replies
15
Views
2K
edromny
E
G
  • Question Question
How to highlight rows based on multiple cells
Replies
2
Views
389
grfbro
G
A
  • Question Question
Custom Dynamic List
Replies
1
Views
755
AmyK023
A

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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