答案1
您的值在单元格中A1,此公式将分离日期部分:
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
丢弃最后一个空格后的所有内容(时间部分)
将其转换为真实日期, 使用:
=DATEVALUE(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))
要“就地”进行这种转换需要一个宏。
编辑#1:
要查找数据中的任何“垃圾”字符,请在单元格中输入示例日期A1.然后在B1进入:
=MID($A$1,ROW(),1)
并复制下来。然后在C1进入:
=CODE(B1)
并复制下来。你应该看到:
编辑#2:
鉴于您的数据A1,此公式:
=--MID(A1,FIND(", ",A1)+2,4)
将返回年份(2015 年). 此公式:
=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12})
将返回月份(7). 此公式:
=--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1)
将返回的那一天(14)。
综合起来:
=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"January","February","March","April","May",",June","July","August","September","October","November","December"},{1,2,3,4,5,6,7,8,9,10,11,12}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))
这个公式避免了与DATEVALUE()。
编辑#3:
正如您所发现的,月份数组必须是已排序,因此请用这个表示月份:
=LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})
这是“最终”公式:
=DATE(--MID(A1,FIND(", ",A1)+2,4),LOOKUP(LEFT(A1,FIND(" ",A1,1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9}),--MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1))
编辑以删除公式中的空格(编辑必须是 6 个字符,因此有此描述)