Extract Date and Time from String

[pendulum]

I'm sure this has never been asked before

Hello.
My data looks like this. Notice that sometimes there is a time, sometimes not, but always a date.

10/29/2022 4:03AM Some more text here

10/29/2022 4:26AM Even more text here

10/29/2022 All this is text

10/29/2022 And some different text here

And so I was using this formula.. (Which I must say, I'm pretty proud that I implemented LET correctly)

=LET(
timestampExtract,
MID(raw!A254,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},raw!A254&"0123456789",1)),LEN(raw!A254)+1)),LOOKUP(1,0*MID(raw!A254,ROW(INDIRECT("1:"&LEN(raw!A254))),1),ROW(INDIRECT("1:"&LEN(raw!A254)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},raw!A254&"0123456789",1)),LEN(raw!A254)+1))),
DATEVALUE(timestampExtract) + TIMEVALUE(timestampExtract)
)

Inefficient, yes, as I run the parsing twice just to get the DATEVALUE and the TIMEVALUE then add them up, but I need them together so I can time-zone convert into another column.

Okay but my problem is that the AM/PM is not being parsed correctly.

Can someone suggest a better way to do this?
Thanks.

 

[jasonb75]

You may not have the TEXTAFTER function yet, if you do then this is the easiest way that comes to mind (untested)

=LOOKUP(1e+100,--TEXTAFTER(Raw!A254," ",{1,2}))

 

[pendulum]

LTSC doesn't have TEXTAFTER yet sadly.

 

[steve the fish]

This works on my machine:

=LET(dt,LEFT(raw!A254,FIND(" ",raw!A254,FIND(" ",raw!A254)+1)-1),IF(OR(RIGHT(dt,2)={"AM","PM"}),LEFT(dt,LEN(dt)-2)+(0.5*(RIGHT(dt,2)="PM")),0+LEFT(dt,FIND(" ",dt&" ")-1)))

 

[pendulum]

This works on my machine:

=LET(dt,LEFT(raw!A254,FIND(" ",raw!A254,FIND(" ",raw!A254)+1)-1),IF(OR(RIGHT(dt,2)={"AM","PM"}),LEFT(dt,LEN(dt)-2)+(0.5*(RIGHT(dt,2)="PM")),0+LEFT(dt,FIND(" ",dt&" ")-1)))

Very nice. You got it.
I give you this award.

Though the other formula will be marked as the solution for posterity.

 

[jasonb75]

Though the other formula will be marked as the solution for posterity.

Why? If you don't have the functions that it uses then it is not a solution, it is a suggestion that you were unable to use.

 

[pendulum]

In the future, that TEXTAFTER function will be more widely available, and the overall formula is more concise and readable, isn't it?

 

[Fluff]

TEXTAFTER function will be more widely available

Only to people with 365, it will never be available on 2021.

 

[jasonb75]

and the overall formula is more concise and readable, isn't it?

It is also not accurate, I've done a quick test since posting the formula and found that it doesn't work.
For starters, I should have used TEXTBEFORE, not TEXTAFTER. Additionally, it requires a space between the time and AM/PM in order to function correctly, meaning that some significant changes would be needed to make it work with the format in your example.

 

[pendulum]

I stand corrected
Thank you