code to extract text from string

[redspanna]

Hi all

Can someone please help to suggest code for following

through column B I have date/time entries like following..

18-05-19 14:40

..and similar through column C

18-05-19 21:57


I'd like the code to.....
extract the date from the string through column B and place into column E
extract the time from the string through column B and place into column F
extract the time from the string through column C and place into column G


so from above example, the required results in column E, F and G would be..


Column E - 18-05-19
Column F - 14:40
Column G - 21:57

Many thanks in advance

 

[sandy666]

are you able to use PowerQuery (Get&Transform)?

example:

Column1	Column2		Column1	List.1	List.2
18/05/2019 14:40​	18/05/2019 21:57​		18/05/2019	14:40:00	21:57:00

 

[Rick Rothstein]

Hi all

Can someone please help to suggest code for following

through column B I have date/time entries like following..

18-05-19 14:40

..and similar through column C

18-05-19 21:57

Is there anything else in the cells with those date/time values or is that the only thing in the cells? If that is the only thing, are your entries actual dates formatted to look the way you showed us or are they text strings?

 

[redspanna]

Is there anything else in the cells with those date/time values or is that the only thing in the cells? If that is the only thing, are your entries actual dates formatted to look the way you showed us or are they text strings?

Yes , that's the only thing in the cells and if I click on B5 that shows following

29-05-19 10:33

in the Formula window it shows

29-05-2019 10:33:00 PM

 

[Norie]

redspanna

Sounds like you have'real' date time values.

Try this.

Sub ExtractDateAndTime()
Dim arrIn As Variant
Dim I As Long

    arrIn = Range("B2", Range("C" & Rows.Count).End(xlUp)).Value

    ReDim arrOut(LBound(arrIn, 1) To UBound(arrIn, 1), 1 To 3)

    For I = LBound(arrIn, 1) To UBound(arrIn, 1)
        arrOut(I, 1) = DateValue(arrIn(I, 1))
        arrOut(I, 2) = TimeValue(arrIn(I, 1))
        arrOut(I, 3) = TimeValue(arrIn(I, 2))
    Next I

    Range("E2").Resize(UBound(arrOut, 1), 3).Value = arrOut

End Sub

 

[redspanna]

Sorry for late reply Norie, however thanks for your help with the code, works great