Convert non standard text to date using Appscript-CodePudding

I know it's a very similar question at first, but I didn't find any question about converting an arbitrary non standard text to date format. This question is more about parsing a string then it's about converting to date format.

I have several Google sheets where the Column Date has many dates registered as a text in the style '16th day of February 2022', and not as a date. No Google sheet tool or any built-in method for java seems to be naturally capable of identify this arbitrary date format as a date.

In python, I could do something like

import pandas as pd
df=pd.read_excel('filename.xlsx')
months={'january':01,'february':02,'etc:10'}
for index,row in df.iterrows():
   arbritary_date= row['Date'].split()
   for i in arbritary_date:
      if i in months:
          date= arbritary_date[0][:2] '/' str(months[i]) arbritary_date[4]
          #and then convert it to date using a built-in library.

But I'm trying to write this code using Appscript, so it would be much faster. Which is being very hard, because I'm still learning Java.

CodePudding user response：

try:

=ARRAYFORMULA(TO_DATE(IFNA(REGEXREPLACE(IF(A1:A="", NA(), A1:A), 
 "(\d ). ?(of)(.  \d{4})", "$1$3")*1)))

update:

demo sheet