Remove Text after particular timing

[Vikas Kumar]

Hi,

I have some text in column A similar following table and I want to extract timings after 04:00 PM,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Timings
[/TD]
[TD]Output
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="319"></colgroup><tbody>
[TD="class: xl63, width: 319"]03:15 PM, 06:15 PM, 09:15 PM
[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 190"]
<colgroup><col width="190"></colgroup><tbody>[TR]
[TD="class: xl65, width: 190"]06:15 PM, 09:15 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 319"]
<colgroup><col width="319"></colgroup><tbody>[TR]
[TD="class: xl65, width: 319"]10:25 AM, 01:30 PM, 04:35 PM, 07:40 PM, 10:45 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 190"]
<colgroup><col width="190"></colgroup><tbody>[TR]
[TD="class: xl65, width: 190"]04:35 PM, 07:40 PM, 10:45 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 319"]
<colgroup><col width="319"></colgroup><tbody>[TR]
[TD="class: xl65, width: 319"]09:30 AM, 12:45 PM, 04:00 PM, 07:15 PM, 10:30 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 190"]
<colgroup><col width="190"></colgroup><tbody>[TR]
[TD="class: xl65, width: 190"]04:00 PM, 07:15 PM, 10:30 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="319"></colgroup><tbody>
[TD="class: xl65, width: 319"] 06:30 PM
[/TD]

</tbody>

[/TD]
[TD]06:30 PM
[/TD]
[/TR]
</tbody>[/TABLE]

As per above sample I would like to remove timing before 04:00 PM and remaining would be Output.

Thanks in advance for help.

 

[Peter_SSs]

A couple of possible options. May need a little tweaking in relation to that last item in your sample depending on whether that is a text entry or a numerical entry formatted as Time.

A) A user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below in B2 and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function After4PM(r As Range) As String
  Dim itm As Variant
  
  For Each itm In Split(r.Text, ", ")
    If CDate(itm) >= TimeSerial(16, 0, 0) Then
      After4PM = After4PM & ", " & itm
    End If
  Next itm
  After4PM = Mid(After4PM, 3)
End Function

B) A worksheet formula if you have the TEXTJOIN function as shown in C2

Excel Workbook
	A	B	C
1	Timings	Output	Output
2	03:15 PM, 06:15 PM, 09:15 PM	06:15 PM, 09:15 PM	06:15 PM, 09:15 PM
3	10:25 AM, 01:30 PM, 04:35 PM, 07:40 PM, 10:45 PM	04:35 PM, 07:40 PM, 10:45 PM	04:35 PM, 07:40 PM, 10:45 PM
4	09:30 AM, 12:45 PM, 04:00 PM, 07:15 PM, 10:30 PM	04:00 PM, 07:15 PM, 10:30 PM	04:00 PM, 07:15 PM, 10:30 PM
5	06:30 PM	06:30 PM	06:30 PM
Sheet1

 

[Vikas Kumar]

Thanks for reply Peter. The UDF is really amazing and this did the trick. But since I am using Excel 2010 version get '#Name' error by formula.

 

[Peter_SSs]

Edit: I hadn't seen your last post so this amendment to the column C formula won't be any good to your either. But it may be of interest to other readers.
Glad the UDF was useful.

Forgot to mention that the column C formula in my last post (& this post) assumes no more than 10 times in a cell. If there could be more, an adjustment to that formula, or this shorter one, would be needed.

Excel Workbook
	A	B	C
1	Timings	Output	Output
2	03:15 PM, 06:15 PM, 09:15 PM	06:15 PM, 09:15 PM	06:15 PM, 09:15 PM
3	10:25 AM, 01:30 PM, 04:35 PM, 07:40 PM, 10:45 PM	04:35 PM, 07:40 PM, 10:45 PM	04:35 PM, 07:40 PM, 10:45 PM
4	09:30 AM, 12:45 PM, 04:00 PM, 07:15 PM, 10:30 PM	04:00 PM, 07:15 PM, 10:30 PM	04:00 PM, 07:15 PM, 10:30 PM
5	06:30 PM	06:30 PM	06:30 PM
Sheet1