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.
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 This Sheet.
={INDEX(SPLIT(TRIM(REGEXEXTRACT(A2," ../.+"))," "),1,1),CONCATENATE(TEXT(INDEX(SPLIT(TRIM(REGEXEXTRACT(A2," ../.+"))," "),1,2),"h:mm")," ",INDEX(SPLIT(TRIM(REGEXEXTRACT(A2," ../.+"))," "),1,3))}
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 each 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 Extract text, time, and date all at once.
Paste this in B3.
={TRIM(SUBSTITUTE(TRIM(A3),TRIM(REGEXEXTRACT(A3," ../.+")),"")),INDEX(SPLIT(TRIM(REGEXEXTRACT(A3," ../.+"))," "),1,1),CONCATENATE(TEXT(INDEX(SPLIT(TRIM(REGEXEXTRACT(A3," ../.+"))," "),1,2),"h:mm")," ",INDEX(SPLIT(TRIM(REGEXEXTRACT(A3," ../.+"))," "),1,3))}
Explanation
Option 02 is the same as Option 01 but we add this TRIM(SUBSTITUTE(TRIM(A3),TRIM(REGEXEXTRACT(A3," ../.+")),"")) in the start of the Array {} to get the text, we basically SUBSTITUTE the outputed date from this formula TRIM(REGEXEXTRACT(A3," ../.+")) with "" nothing.
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")}
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})")