在Excel单元格中解析非标准日期格式 [英] Parsing non-standard date format in Excel cell

问题描述

我想将格式为"2017年3月30日，05:00:00.000"的日期转换为Excel日期值?使用单元格公式而不是VBA可以做到的最优雅的解决方案是什么?

I want to convert dates formatted like "March 30th 2017, 05:00:00.000" to an excel date value? What's the most elegant solution I can do this with using a cell-formula and not VBA?

推荐答案

这将执行标准的"rd","th","st","nd"

=--(LEFT(A1,MIN(FIND({"rd","th","st","nd"},A1 & "thrdstnd"))-1)& ", " & SUBSTITUTE(MID(A1, MIN(FIND({"rd","th","st","nd"},A1 & "thrdstnd"))+2,LEN(A1)),",",""))

您可以根据需要在公式中添加其他后缀

You can add other suffixes as you need to the formula

然后，您可以根据需要设置其格式.

Then you can format it as you like.

这篇关于在Excel单元格中解析非标准日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！