☰

在 Excel 单元格中提取具有随机模式的日期

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 单元格中提取具有随机模式的日期

您是否有更简单或更通用的方法来提取/格式化 B 格式的 Excel 单元格。A 列具有随机模式，如 A 列中所示。

A                      B
16/06/2009            6/16/2009
11/6/2009             6/11/2009
29/10/2009 13:12:51   10/29/2009 13:12:51
3/12/2009 9:38        12/3/2009  9:38

我尝试了这个解决方案，但它不适用于第二种情况。

B = MID($A1,4,2) & "/" & MID($A1,1,2) & "/" & MID($A1,7,4)

另外，在我的情况下，宏是不可能的。我只能使用原生的 Excel 函数。谢谢。

编辑 1：我更新了问题，以涵盖我刚从数据中了解到的新模式。抱歉。

编辑 2：我找到了解决方案，请参阅答案 #2。这有点蛮力，但在所有情况下都有效。

答案1

假设我们在列中有一些日期A实际上文本采用 dd/mm/yyyy 格式。

在B1进入：

=DATE(RIGHT(A1,4),TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",999)),1000,999)),LEFT(A1,FIND("/",A1)-1))

并抄下来。然后将适当的格式应用到列乙：

在此处输入图片描述

此公式将正确处理列中的日期A采用以下格式：

  • 日/月/年
  • 日/月/年
  • 日/月/年
  • 日/月/年

编辑＃1：

如果列中的数据A是真正的 Excel 日期，然后使用它B1：

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

答案2

今天早上我睡了一觉，喝了几杯咖啡，然后我设法解决了这个问题。它是您输入的内容和我读到的内容的结合。

这些单元格所代表的公式：

E2 = 27/06/2016 08:00:00
F2 = FIND("/",$E2) -> 3 the first /
G2 = FIND("/",$E2,4) -> 6 the second /
H2 = IFERROR(FIND(" ",$E2,4),0) -> 11 any presence of " " space
I2 = NUMBERVALUE(MID($E2,$F2+1,($G2-$F2)-1)) -> 6 month
J2 = NUMBERVALUE(MID($E2,1,$F2-1)) -> 27 day
K2 = NUMBERVALUE(MID($E2,$G2+1,4)) -> 2016 year
L2 = IF(H2=0,"N", "Y") -> Y time present
M2 = IF(H2=0,"00:00:00",RIGHT($E2,LEN($E2)-$H2)) -> 08:00:00 time
N2 = I2 &"/"& J2 &"/"& K2 &" "& M2 -> 6/27/2016 08:00:00 final results
  1. 我从 Excel 中复制了整列并粘贴到记事本上。
  2. 格式化日期（例如 E2），删除所有行并格式化为文本。
  3. 将记事本中的所有行粘贴到 E2 列中。这强制 Excel 将该列解释为文本！他妈的
  4. 然后我得到了公式，我将它们分解以测试是否所有情况都有效。

我在这里分享了我的结果

相关内容