解析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屋！