Regex to remove time zone stamp

Question

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:/' ' ].*

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!

user_3832970 · Answer

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.

user_5866580 · Answer

With your shown samples, attempts please try following regex in REGEXREPLACE. This will help to match time stamp specifically. Here is the Online demo for following regex. This will create only 1 capturing group with which we are replacing the whole value(as per requirement).

=REGEXREPLACE(A1, "^((?:\d{1,2}\/){2}\d{4}\s+(?:\d{1,2}:){2}\d{1,2}).*", "$1")

Explanation: Adding detailed explanation for above used regex.

^(                    ##Matching from starting of the value and creating/opening one and only capturing group.
   (?:\d{1,2}\/){2}   ##Creating a non-capturing group and matching 1 to 2 digits followed by / with 2 times occurrence here.
   \d{4}\s+           ##Matching 4 digits occurrence followed by 1 or more spaces here.
   (?:\d{1,2}:){2}    ##In a non-capturing group matching 1 to 2 occurrence of digits followed by colon and this combination should occur2 times.
   \d{1,2}            ##Matching 1 to 2 occurrences of digits.
)                     ##Closing capturing group here.
.*                    ##This will match everything till last but its not captured.

user_12254098 · Answer

You can do this without REGEX by simply splitting and adding the first and second index.

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   INDEX(SPLIT(A2:A," "),0,1)+
   INDEX(SPLIT(A2:A," "),0,2)))

Regex to remove time zone stamp

3 Answers