VB :: Break Down Cell Into Multiple Cells and Categories

[Craig.Cochran]

I have an old Excel sheet that breaks down my employee's tasks throughout the day. The issue I have is that the tasks are bundled within one cell and I would like to use VB to extract the data as indicated below:

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=70 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2986" width=70><TBODY><TR style="HEIGHT: 123.75pt" height=165><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 123.75pt; BACKGROUND-COLOR: transparent" width=70 height=165>07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 272pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=362 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 3072" width=72><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 6186" span=2 width=145><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #3366ff" width=72 height=15>Task 1 - Start</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 109pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff" width=145>Task 1 - End</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 109pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff" width=145>Task 1 - Category</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num="0.29166666666666669">7:00</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.45833333333333331">11:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">On Duty</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #3366ff" height=15>Task 2 - Start</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 2 - End</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 2 - Category</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num="0.45833333333333331">11:00</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.47916666666666669">11:30</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Lunch</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #3366ff" height=15>Task 3 - Start</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 3 - End</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 3 - Category</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num="0.47916666666666669">11:30</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.48958333333333331">11:45</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Conference Call</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #3366ff" height=15>Task 4 - Start</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 4 - End</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #3366ff">Task 4 - Category</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num="0.48958333333333331">11:45</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.625">15:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">On Duty</TD></TR></TBODY></TABLE>


Note: Not all employees have the same number of tasks, nor the same type.

Any assistance with this would be greatly appreciated!

Respectfully,

Craig Cochran

 

[GTO]

Is the 'before' data shown all in one cell, like:
<TABLE style="WIDTH: 128pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=170 border=0 x:str><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><TBODY><TR style="HEIGHT: 51.75pt" height=69><TD class=xl22 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: transparent" width=170 height=69>07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)</TD></TR></TBODY></TABLE>

 

[Craig.Cochran]

Yes - the information is simply spaced out and the format of the cell is wrapped.

 

[GTO]

Simply as a start, to see if we can get a dependable pattern. For:

Excel Workbook
	B	C	D	E	F
1	Before		After
2	07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)	Task 1	7:00:00 AM	11:00:00 AM	On Duty
3	07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)	Task 1	11:00:00 AM	11:30:00 AM	Lunch
4	07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)	Task 1	11:30:00 AM	11:45:00 AM	Conference Call
5		Task 1	11:45:00 AM	3:00:00 PM	On Duty
6
7
8
Sheet1

Excel 2003
Maybe:

Option Explicit
    
Sub SplitText()
Dim REX             As Object '<--- RegExp
Dim rexMatchCol     As Object '<--- MatchCollection
Dim rexMatch        As Object '<--- Match
Dim wks             As Worksheet
Dim rngData         As Range
Dim Cell            As Range
Dim vntStart        As Variant
Dim vntEnd          As Variant
Dim strHours        As String
Dim strDuty         As String
Dim lRowOffset      As Long
Dim lTask           As Long
Dim aryTimes        As Variant
    
    
    Set wks = ThisWorkbook.Worksheets("Sheet1") '<---Change name to suit
    Set rngData = Range(wks.Cells(2, 2), wks.Cells(wks.Rows.Count, 2).End(xlUp))
    Set REX = CreateObject("VBScript.RegExp")
    
    With REX
        .Global = True
        .Pattern = "([0-9\ \:\-]+?\()([A-Za-z\ ]+\))"
        
        lRowOffset = 0
        For Each Cell In rngData
        lTask = lTask + 1
            If .Test(Cell.Value) Then
                Set rexMatchCol = .Execute(Cell.Value)
                
                For Each rexMatch In rexMatchCol
                    strHours = Replace(rexMatch.SubMatches(0), "(", vbNullString)
                    aryTimes = Split(strHours, "-")
                    vntStart = Trim(aryTimes(0))
                    vntEnd = Trim(aryTimes(1))
                    strDuty = Replace(rexMatch.SubMatches(1), ")", vbNullString)
                        
                    Cell.Offset(lRowOffset, 1).Value = "Task " & lTask
                    Cell.Offset(lRowOffset, 2).Value = CDate(vntStart)
                    Cell.Offset(lRowOffset, 3).Value = CDate(vntEnd)
                    Cell.Offset(lRowOffset, 4).Value = strDuty
                    lRowOffset = lRowOffset + 1
                Next
                lRowOffset = lRowOffset - 1
            End If
        Next
    End With
End Sub

I would suggest testing (in a junk copy of your wb) to see if the .Pattern jambs on anything possibilities you may have not yet noticed.

Again, just a start, as hopefully others will join and show no doubt better RegExp.

Mark

 

[Craig.Cochran]

Thanks Mark,

I will be giving this a try and posting back.

Respectfully,

Craig

 

[Craig.Cochran]

This does work well for what I am trying to accomplish - I am sure that I will be able to use simple VB to extract and place the data from here. The only concern I have is when I input more than five timed "duties", it is not populating down. At times, these employees will be scheduled for a lot of conference calls throughout a shift.

Thank you much for the help thus far!

 

[Peter_SSs]

Not quite sure how the original data is laid out or where the results should go but see if this is any help. It assumes original data is in column A starting in A2 and lays out results across the sheet as shown below.

Code:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Tasks()<br>    <SPAN style="color:#00007F">Dim</SPAN> cel <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Bits, Headings<br>    <br>    Headings = Array("Task # - Start", "Task # - End", "Task # - Category")<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>        c = c + 4: r = 1: t = 0<br>        s = cel.Value<br>        s = Replace(Replace(s, "(", ")"), " - ", ")")<br>        Bits = Split(s, ")")<br>        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Bits) - 1 <SPAN style="color:#00007F">Step</SPAN> 3<br>            t = t + 1<br>            <SPAN style="color:#00007F">With</SPAN> Cells(r, c).Resize(, 3)<br>                .Value = Headings<br>                .Replace What:="#", Replacement:=t, Lookat:=xlPart, _<br>                    SearchFormat:=False, Replace<SPAN style="color:#00007F">For</SPAN>mat:=False<br>                .Interior.ColorIndex = 41<br>                .Font.ColorIndex = 2<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            For j = 0 <SPAN style="color:#00007F">To</SPAN> 2<br>                Cells(r + 1, c + j).Value = Bits(i + j)<br>            <SPAN style="color:#00007F">Next</SPAN> j<br>            r = r + 2<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">With</SPAN> Columns(c).Resize(, 3)<br>            .AutoFit<br>            .HorizontalAlignment = xlLeft<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> cel<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


My sample data and results:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Data			Task 1 - Start	Task 1 - End	Task 1 - Category		Task 1 - Start	Task 1 - End	Task 1 - Category
2	07:00 - 11:00 (On Duty) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Conference Call) 11:45 - 15:00 (On Duty)			7:00	11:00	On Duty		7:00	11:30	xyz
3	07:00 - 11:30 (xyz) 11:00 - 11:30 (Lunch) 11:30 - 11:45 (Call the President) 11:45 - 15:00 (On Duty) 15:15 - 15:30 (Break)			Task 2 - Start	Task 2 - End	Task 2 - Category		Task 2 - Start	Task 2 - End	Task 2 - Category
4				11:00	11:30	Lunch		11:00	11:30	Lunch
5				Task 3 - Start	Task 3 - End	Task 3 - Category		Task 3 - Start	Task 3 - End	Task 3 - Category
6				11:30	11:45	Conference Call		11:30	11:45	Call the President
7				Task 4 - Start	Task 4 - End	Task 4 - Category		Task 4 - Start	Task 4 - End	Task 4 - Category
8				11:45	15:00	On Duty		11:45	15:00	On Duty
9								Task 5 - Start	Task 5 - End	Task 5 - Category
10								15:15	15:30	Break
11
Tasks

 

[Peter_SSs]

Forgot to mention that my suggestion also relies on the string " - " not occurring anywhere other than to separate the start/end time of a task.

(Mark: Thanks for the PM but from the single data sample given, I didn't see Regular Expressions as the best way to attack this one )