CODEWITHSUNDEEP
regexgoogle-sheets-formula
Evgeniy
Evgeniy

Reputation: 2605

What is the replacement in REGEXREPLACE for replacing with matched string?

I have a date format Wed, 09 Dec 2020 10:57:16 GMT, which i want turn into 09 Dec 2020.

My matching RegEx is [0-9]{2}\s\D{3}\s[0-9]{4}.

With what should i replace my match in the formula =to_date(regexreplace(B2,"([0-9]{2}\s[a-z]{3}\s[0-9]{4})","$1")) instead of $1?

I've tried some variants from https://www.regular-expressions.info/refreplacebackref.html, like $MATCH or $&, but failed. Using it i get only Wed, $& 10:57:16 GMT

Upvotes: 0

Views: 71

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627469

You can use REGEXEXTRACT that extracts the match from the given string:

=REGEXEXTRACT(B2,"\b[0-9]{2}\s\D{3}\s[0-9]{4}\b")

enter image description here

Note I added \b word boundaries to make sure the match is not in between word chars.

Or, fix your REGEXREPLACE using

=TO_DATE(REGEXREPLACE(B2,".*?\b([0-9]{2}\s\D{3}\s[0-9]{4})\b.*","$1"))

enter image description here

Here, the .*? matches any chars other than line break chars as few as possible and .* at the end just matches all the rest of the string.

Upvotes: 1

Related Questions