Trying to separate date and time alone from the text

"Reported By:  Name 1   16/2/2021 9:44 AM"

Used the formula

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

But, The output is 6/2/2021

Also haven't figured out the time part yet

CodePudding user response：

Answer

The following formula should have the result you desire:

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

Alternatively, this formula also returns the time:

=REGEXEXTRACT(A19,"\d{1,2}/\d{1,2}/\d{4} \d{1,2}:\d{2} [A|P]M")

And this one puts the date and time in separate cells:

={REGEXEXTRACT(A19,"\d{1,2}/\d{1,2}/\d{4}"),REGEXEXTRACT(A19,"\d{1,2}:\d{2} [A|P]M")}

Explanation

In the formula you were using, the non-capturing group that begins your regular expression contains . . The . matches any character and the means that it looks for one or more matches. The quantifier is greedy, meaning that it will match as much as possible.

As the regular expression begins processing, it starts matching every character from the beginning of the string, eventually matching the entire string. Then the regex begins trying to match (\d{1,2}/\d{1,2}/\d{4}) but it can't because the entire string has already been matched. However, the regex notices it can backtrack with the . , which starts giving up characters from the end of the string backwards. Eventually (\d{1,2}/\d{1,2}/\d{4}) becomes fully matched when regex tries 6/2/2021. Because (\d{1,2}/\d{1,2}/\d{4}) has been fully matched, . stops giving up its characters because of its greedy behaviour.

In short, because . can match Reported By: Name 1 1 (including the first digit of the date), it will.

The new regex I provide doesn't bother with the non-capturing group and so it doesn't have that issue. If a non-capturing group is necessary for some reason, you could use the following instead, adding a space in the non-capturing group.

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

CodePudding user response：

Option 01 Extract Date and time Onl
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