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

Question

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

Exceed

Answer 1

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

Bout

I am really poor in Regex. How can I develop my regex knowledge? – Polytechnic

What will be the 3rd permitter for REGEXREPLACE? I get return a #VALUE! from your solution. – Exceed

@AmirHossain you can leave 3rd parameter of regexreplace empty or use "". check the images posted in answer. after 2nd parameter there is a comma , and nothing after a comma – Bout

@Harun24HR all guides out there are old and poor including this one I started with github.com/google/re2/wiki/Syntax ppl recon regexr.com , regex101.com , regexone.com but google sheets regex is a bit different - limited so not everything can be learned there – Bout

Answer 2

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

Polytechnic

Thank you for your reply. I'll try this soon. – Exceed