Convert Text-Formatted "Day Name, Month Name, Date of Month, Year" to "YYYY-MM-DD&quo-CodePudding

I have an Excel sheet with thousands of dates saved in Text format, written the following way:

I want these cells to be converted into "YYYY-MM-DD" format. However, Excel cannot detect these cells as Date Format; hence, when I try to use the "Format Cells" (or Ctrl 1) to change the date format, it does not work and stays the same. I need them to be in "YYYY-MM-DD" because I will be using the date in multiple functions throughout my workbook and the Text formatted Dates do not allow that.

CodePudding user response：

You may try FILTERXML() in this way.

=FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>",1)&"</s></t>","//s[last()]")

Then format resulting cells as YYYY-MM-DD. Or you can use TEXT() function to get output directly as date.

=TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>",1)&"</s></t>","//s[last()]"),"YYYY-MM-DD")

CodePudding user response：

In addition to Harun's answer. For if one does not have Windows one could use: =TEXT(MID(A1,FIND(",",A1) 2,LEN(A1)),"yyyy-mm-dd")