Excel技巧：四步搞定不规范日期格式转换

Excel技巧：四步搞定不规范日期格式转换

在Excel中处理不规范的日期格式数据时，经常会遇到各种各样的问题。例如，有些日期格式看起来很相似，但实际上可能多一个空格或少一个字符，很难发现。本文将介绍如何将这些不规范的日期格式转换为标准的"YYYY-MM-DD"格式，并判断格式是否正确。

假设我们希望的理想格式是1980-01-01，即"YYYY-MM-DD"格式，共10位。但在实际数据表中，可能会遇到以下各种格式：

• 1988.9.10
• 1985-01-26
• 09 0CT1978
• Jan.25,1975
• 15/11/1988
• 1986年12月26
• 19850430
• 28-Mar-70
• 31 OCT 1990

我们的目标是：能规范的尽量规范，不能规范的标记为"格式错误"，以便之后人工处理。

具体处理步骤如下：

第1步：将原始数据复制到A列。在B列处理8位格式的日期，如19850430，需要加上分隔符：

=IF(LEN(A2)=8,(MID(A2,1,4)&"-"&MID(A2,5,2)&"-"&MID(A2,7,2)),A2)

第2步：在C列进行第一次转换：

=TEXT(B2, "YYYY-MM-dd")

第3步：在D列将所有的点号"."替换为短横线"-":

=SUBSTITUTE(C2, ".", "-")

第4步：在E列判断结果格式是否正确，主要检查第5位和第7位是否是连接线"-"，同时检查是否包含"–"和"/":

=IF(AND(MID(D2,5,1)="-",MID(D2,8,1)="-",ISNUMBER(FIND("--",D2))=FALSE,ISNUMBER(FIND("/",D2))=FALSE),"OK","格式错误")

通过以上步骤，我们可以将大部分不规范的日期格式转换为标准格式，并标记出无法转换的格式，以便后续人工处理。

本文原文来自CSDN