CODEWITHSUNDEEP
regexgoogle-sheetsformulagoogle-sheets-formula
Valera Lexiutchin
Valera Lexiutchin

Reputation: 23

Extracting day,month,2-digits year from text in Google Sheets using Regexextract

would appreciate any help.

This is how the text looks like 11jun2018/M , 16JUL1976/F(it's date of birth+male or female)

I need to get the next result: 11jun18 , 16jul76.

I tried \d{2}\w{3}\d{4} which results in 11jun2018 and i also found what \d{2}(\d{2}) results in 18 ,but i can't figure out how to unite those two regex.

Because when i unite,i get previous result(only 18,instead of 11jun18).

Would also appreciate some regex guide for dummies

Upvotes: 2

Views: 1218

Answers (3)

Valera Lexiutchin
Valera Lexiutchin

Reputation: 23

Tnx to Wiktor Stribiżew formula i tried some more things and here that i came up with:

1.I did what I wanted from the start,unite my 2 regex: \d{2}\w{3}\d{4} and \d{2}(\d{2}) into =REGEXEXTRACT(A1,"(\d{2}\w{3})\d{2}(\d{2})") but it wrote answer into 2 cells(B51,C51) as you can see on screenshot.

-I want to remind that cell C51 doesn't contain any formula.

We can just divide formula from B51 into two regexextract formulas and concatenate them.

Like this =REGEXEXTRACT(A1,"\d{2}\w{3}")&REGEXEXTRACT(A1,"\d{2}(\d{2})")

=REGEXEXTRACT(A1,"\d{2}\w{3}") results in 06JUL and =REGEXEXTRACT(A1,"\d{2}(\d{2})") results in 77

See the Link for Google Sheets below

2.If you don't like the first method we can also do this:

Since we are getting response with array from REGEXEXTRACT function we can just get rid of it using function JOIN or TEXTJOIN

=JOIN("",REGEXEXTRACT(A1,"(\d{2}\w{3})\d{2}(\d{2})"))

I decided to better give link to Google Sheet for whose who are interested in checking solution I mentioned above, instead of making ton of screenshots.

Upvotes: 0

JvdV
JvdV

Reputation: 75860

You could try:

enter image description here

The formula used:

=ARRAYFORMULA(LEFT(REPLACE(LOWER(FILTER(A1:A,A1:A<>"")),6,2,),7))

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626893

You may use

=LOWER(REGEXREPLACE(A1,"^(\d{2}[a-zA-Z]{3})\d{2}(\d{2})/[MF]$", "$1$2"))

If there must be an empty result if there is no match add |.+ at the end:

=LOWER(REGEXREPLACE(A1,"^(\d{2}[a-zA-Z]{3})\d{2}(\d{2})/[MF]$|.+", "$1$2"))

Here,

  • ^ - matches start of string
  • (\d{2}[a-zA-Z]{3}) - captures 2 digits and 3 letters into Group 1 ($1)
  • \d{2} - matches two digits
  • (\d{2}) - captures two digits into Group 2 ($2)
  • / - a slash
  • [MF] - M or F is matched
  • $ - end of string
  • |.+ - or matches any other string, 1 or more chars other than line break chars as many as possible.

See screenshot:

enter image description here

Upvotes: 3

Related Questions