CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formularegex-groupregex-greedy
Emye DaOne
Emye DaOne

Reputation: 11

How do I regextract the second date in a string?

I am trying to extract the second date displayed in this string, however my code keeps extracting just the first date in gsheet:

String: BOT +1 1/1 CUSTOM IWM 100 12 SEP 22/7 SEP 22 184/184 PUT/CALL @6.13

This is my code: =REGEXEXTRACT(A3,"(\d{1,2}\s+[A-Za-z]+\s\d{2,4})")

my result: 12 SEP 22

Desired result should be: 7 SEP 22

Appreciate the help, thanks in advance!

Upvotes: 0

Views: 102

Answers (3)

hossein hayati
hossein hayati

Reputation: 1158

To extract just the second date, you can modify the code as follows:

=REGEXEXTRACT(A3,"\d{1,2}\s+[A-Za-z]+\s\d{2,4}.*(\d{1,2}\s+[A-Za-z]+\s\d{2,4})")

This regular expression \d{1,2}\s+[A-Za-z]+\s\d{2,4}.*(\d{1,2}\s+[A-Za-z]+\s\d{2,4}) will match the first date and the second date in the string, and then extract just the second date.

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 30260

Here's one approach to dynamically extract N number of dates within your string OR extract the 2nd or 3rd date pattern as per the requirement.

=index(if(len(A:A),lambda(y,regexextract(y,lambda(z,regexreplace(y,"(?i)("&z&")","($1)"))("\d{1,2}\s"&JOIN("\s\d{2}|\d{1,2}\s",INDEX(TEXT(SEQUENCE(12,1,DATE(2022,1,1),31),"MMM")))&"\s\d{2}")))(regexreplace(A:A,"[\(\)/+]","")),))

enter image description here

if its to pick specific number pattern, wrap the formula within index + number as shown in the screenshot

=index(formula,,pattern number)

enter image description here

Upvotes: 0

Martín
Martín

Reputation: 10217

Considering you have already a working formula for detecting dates, you can try adding first outside of the parentheses the same structure. So it will look for the first date, then .+ will consider that there will be some characters in between, and then your working pattern between parenthesis. Then only that last part will be extracted:

=REGEXEXTRACT(A3,"\d{1,2}\s+[A-Za-z]+\s\d{2,4}.+(\d{1,2}\s+[A-Za-z]+\s\d{2,4})")

Upvotes: 0

Related Questions