How to get all cells from column A to reflect with a certain formula in column B-CodePudding

I am trying to get 04 18, 04 19 ... to reflect in column E from column A. note that column A will be filled dynamically by a bot, so i need column E to grow dynamically as well.

the timestamp that the bot provides is in a string format, i want only the date and hour out of the same, because i want to plot a chart with that as the x-axis.

The formula im currently using is

=MID(OFFSET(A2,0,0,COUNTIF(A:A,"*")),8,6)

to get "04 18" out of "Sun Sep 04 18:19:39 UTC 2022"

CodePudding user response：

I have tested the formula below and it works for me.

=IFERROR(IF(AND(VALUE(LEFT(MID(A5,9,5),2))>0,MID(MID(A5,9,5),3,1)=" ",VALUE(RIGHT(MID(A5,9,5),2))>0),MID(A5,9,5),""),"")

CodePudding user response：

If your are on Microsoft-365 with latest release then can use-

=TEXTAFTER(TEXTBEFORE(TOCOL(A2:A100000,1),":")," ",-2)