Search code examples
regexgoogle-sheetspython-datetime

Regex to remove time zone stamp

In Google Sheets, I have time stamps with formats like the following:

5/25/2022 14:13:05
5/25/2022 13:21:07 EDT
5/25/2022 17:07:39 GMT+01:00

I am looking for a regex that will remove everything after the time, so the desired output would be:

5/25/2022 14:13:05
5/25/2022 13:21:07
5/25/2022 17:07:39

I have come up with the following regex after some trial and error, although I am not sure if it is prone to errors: [^0-9:\/' '\n].*

And the function in Google Sheets that I plan to use is REGEXREPLACE().

My goal is to be able to do calculations regardless of one's time zone, however the result will be stamped with the user's local time zone.

Could someone confirm this is correct? Appreciate any feedback I can get!

Solution

  • You can use

    =REGEXREPLACE(A1, "^(\S+\s\S+).*", "$1")
=REGEXREPLACE(A1, "^([\d/]+\s[\d:]+).*", "$1")

    See the regex demo #1 / regex demo #2.

    Details:

    • ^ - start of string
    • (\S+\s\S+) - Group 1: one or more non-whitespaces, one or more whitespaces and one or more non-whitespaces
    • [\d/]+\s[\d:]+ - one or more digits or / chars, a whitespace, one or more digits or colons
    • .* - any zero or more chars other than line break chars as many as possible.

    The $1 is a replacement backreference that refers to the Group 1 value.