Extract date and time from text in GoogleSheets-CodePudding

I am trying to separate the date and time alone from the text Reported By: Name 1 16/2/2021 9:44 AM.

Currently, I am using the following formula:

=REGEXEXTRACT(A19,"(?:. )(\d{1,2}/\d{1,2}/\d{4})")

However, the output is 6/2/2021 instead of 16/2/2021.

I also haven't figured out the time part yet.

CodePudding user response：

Option 01 Extract Date and time Only
Try this in C2, to get time and date extracted to their cells in one go, take a look at

Explanation
1 - in this formula we are matching with REGEXEXTRACT the first slash / and 2 .. charchters before and every thing after the slash . to grab 16/2/2021 9:44 AM

2 - TRIM any additional spaces this way we get the correct output even with diffrent spacing.

3 - SPLIT using " " as a delimiter.

4 - INDEX to get the column for eatch column resulted from the SPLIT function in the Array {} {INDEX(SPLIT(output,,1,1),INDEX(SPLIT(output,,1,2),INDEX(SPLIT(output,,1,3)} Like this,1,1 Row 1 Column1 to get 16/2/2021.

5 - Format the output of column 2 of the SPLIT function which is the "time" 9:44 AM in this example with the TEXT function and set the format "h:mm".

Option 02