I have the following information in Column C:



Monday, January 10, 2017

I want the day in Column D, date in Column E, and the year in Column F

I have been trying to do so using the Text to Columns function but without success. I am using Office 2016.

Thanks for you help

TexasCPA

Try...

D2 =LEFT(C2,FIND(",",C2)-1)

E2 =MID(C2,FIND(",",C2)+2,LEN(C2))+0

F2 =YEAR(E2)

Note: Format E2 as a date

@jeffreybrown

Excuse me, but does that formula in E2 work for you?
I ask because it doesn't work for me. Not quite sure however if that can somehow be due to the fact that I don't have US-settings.

Ciao xor,

Yes, I tested the formulas before I posted.

Data can be parsed to different worksheet according to cell value.

Watch and download example file :https://youtu.be/S-KUHWysAqk

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Column = 8 Then
For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, "H").Value = "On hire" Then
Rows(i).Copy
Sheets("On_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
ElseIf Cells(i, "H").Value = "Off hire" Then
Rows(i).Copy
Sheets("Off_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
ElseIf Cells(i, "H").Value = "On sales" Then
Rows(i).Copy
Sheets("On_sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Rows(i).Delete
i = i - 1
End If
Next i
End If
End Sub