Can you help me split a cell and use first part as a new row heading?

[CastingDirector]

Please consider helping me with this...

Here is what I have on a sheet:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]NAME (B)[/TD]
[TD]Date/time info (C)[/TD]
[TD]Agent (D)[/TD]
[TD][/TD]
[TD]Role/Notes (E)[/TD]
[/TR]
[TR]
[TD]Sam Smith[/TD]
[TD]4-29-2015 5:15 PM[/TD]
[TD]WM[/TD]
[TD][/TD]
[TD]Jimmy/Has Issues[/TD]
[/TR]
[TR]
[TD]Bud Light[/TD]
[TD]4-29 2015 3:12 PM[/TD]
[TD]CAA[/TD]
[TD][/TD]
[TD]Jimmy/No Travel[/TD]
[/TR]
[TR]
[TD]**** Wood[/TD]
[TD]4-23-2015 4:10 PM[/TD]
[TD]Front[/TD]
[TD][/TD]
[TD]Xavier/Not interested[/TD]
[/TR]
[TR]
[TD]Al Kida[/TD]
[TD]4-4-2015 2:00 PM[/TD]
[TD]Manager[/TD]
[TD][/TD]
[TD]Paul/Wife pregnant[/TD]
[/TR]
</tbody>[/TABLE]


So here is what I need in VBA sheet module... Please note: Column E (Role/Notes) should split, then first part is moved to make new row heading in bold and in alphabetical order.
The second part of string remains in the cell. Additional data is frequently copied from another sheet into this as well so it would need to accommodate new data over time.
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]NAME (B)[/TD]
[TD]Date/Time Info(C)[/TD]
[TD]Agent(D)[/TD]
[TD][/TD]
[TD]Role/Notes(E)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bud Light[/TD]
[TD]4-29-2015 4:10 PM[/TD]
[TD]CAA[/TD]
[TD][/TD]
[TD]No Travel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam Smith[/TD]
[TD]4-29-2015 5:15 PM[/TD]
[TD]WM[/TD]
[TD][/TD]
[TD]Has Issues[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Al Kida[/TD]
[TD]4-4-2015 2:00 PM[/TD]
[TD]Manager[/TD]
[TD][/TD]
[TD]Wife Pregnant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Xavier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]**** Wood[/TD]
[TD]4-23-15 4:10PM[/TD]
[TD]Front[/TD]
[TD][/TD]
[TD]Not Interested[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am in the final leg of this 2 year journey to make this right (no kidding). Your help means everything to me. Is this do-able? Does it make sense?

 

[CastingDirector]

The only way I could get the error message was to put the code into a sheet code module and run the code twice without resetting the rows. The code should be in the standard module 1, not the sheet code module.

Thank you JLGWhiz This works wonderfully well. I, in fact had it in the wrong module....Please accept my sincere gratitude for sticking with me as well as Soggy's guidance to make this work!

SOLVED

 

[JLGWhiz]

Thank you JLGWhiz This works wonderfully well. I, in fact had it in the wrong module....Please accept my sincere gratitude for sticking with me as well as Soggy's guidance to make this work!

SOLVED

Glad you got it going,
Regards, JLG