How do I regextract the second date in a string?

Question

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!

Does the second date in `12 SEP 22/7 SEP 22` has to be preceded by a slash `/` in any string ? — SaSkY, Feb 11 '23 at 03:01

score 0 · Answer 1 · answered Feb 11 '23 at 03:29

0

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})")

answered Feb 11 '23 at 03:29

Martín

7,849
2
3
13

Thank you so much, that worked! Let's say the string has four dates this time around, how would I then extract each of the dates and place them in their own individual cells? String: BOT +1 1/-1/1/-1 CUSTOM SPY 100 (Quarterly) 10 DEC 23/4 DEC 23/12 OCT 22/8 MAR 23 324/330/382/389 CALL/CALL/PUT/PUT @ 3.58 Thanks in advance! – Emye DaOne Feb 11 '23 at 03:50

score 0 · Answer 2 · answered Feb 11 '23 at 10:41

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,"[\(\)/+]","")),))

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

=index(formula,,pattern number)

score 0 · Answer 3 · answered Feb 11 '23 at 11:39

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.

How do I regextract the second date in a string?

3 Answers3