Convert String (16 February 2022 20 h 50 min) into date-time format in Google Sheet-CodePudding

I am trying to convert a string into date format in Google Sheet. I've tried different formulas but failed. I guess REGEXMATCH may solve this or even Google Apps Script can also solve this. But I prefer formulas if possible. Please advise your valuable opinion.

I have the string in the following format:

16 February 2022 20 h 50 min

I want it converted as follows:

16/02/2022 20:50:00

CodePudding user response：

Give a try on below formula-

=TEXT(DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1," h ",":")," min","")) TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1," h ",":")," min","")),"dd/mm/e hh:mm:ss")

CodePudding user response：

to convert it into true date:

=SUBSTITUTE(REGEXREPLACE(A1, " (h|min)", ), " ", ":", 4)*1

or directly:

=TEXT(SUBSTITUTE(REGEXREPLACE(A1, " (h|min)", ), " ", ":", 4)*1, "dd/mm/e hh:mm:ss")