Google spreadsheets: transform text to date and time, how?

Question

Google Sheets

An example of text that needs to be converted to a date and time is January 07, 2021 at 08:31PM , how to make it look like this: 20:31, 01/07/2020 ?

Answer 1

Maybe,

=DATE(
  REGEXEXTRACT(A1,"(\d{4})"),
  SWITCH(REGEXEXTRACT(A1,"\w{3}"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11),
  REGEXEXTRACT(A1,"(\d{2}),")
)
+
TIME(
  IF(REGEXMATCH(A1,"PM"),12,0)+REGEXEXTRACT(A1,"(\d{2}):"),
  REGEXEXTRACT(A1,":(\d{2})"),
  0
)

Unfortunately, all this strongly depends on the string format.
Array Formula

=ARRAYFORMULA(
  DATE(
    REGEXEXTRACT(A1:A12,"(\d{4})"),
    SWITCH(REGEXEXTRACT(A1:A12,"\w{3}"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11),
    REGEXEXTRACT(A1:A12,"(\d{2}),")
  )
  +
  TIME(
    IF(REGEXMATCH(A1:A12,"PM"),12,0)+REGEXEXTRACT(A1:A12,"(\d{2}):"),
    REGEXEXTRACT(A1:A12,":(\d{2})"),
    0
  ))

Perhaps SWITCHnot the best idea. Can be replaced withMATCH

=ARRAYFORMULA(
  DATE(
    REGEXEXTRACT(A2:A100;"(\d{4})");
    MATCH(REGEXEXTRACT(A2:A100;"\w{3}");{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)-1;
    REGEXEXTRACT(A2:A100;"(\d{2}),")
  )
 +
  TIME(
    IF(REGEXMATCH(A2:A100;"PM");12;0)+REGEXEXTRACT(A2:A100;"(\d{2}):");
    REGEXEXTRACT(A2:A100;":(\d{2})");
    0
  )
 )

Answer 2

I "parsed" such a format using the functions REGEXTRACT (), MATCH () (for replacing the name of the month with its number), RIGHT (), LEFT (), IF () (for checking AM / PM). Since the received text in the form of a date was enough for me, I did not use DATEVALUE ().