☰

Excel 无法将文本字符串识别为日期

tag-icon • tag-icon microsoft-excel date-format
Excel 无法将文本字符串识别为日期

我正在尝试从外部源导入大量日期，其中包含日期字段作为文本格式：-

2021 年 9 月 23 日

2021 年 9 月 22 日

等等。我希望 Excel 将这些日期识别为日期，但不幸的是，它不能，它仍然将它们视为文本。我尝试将其拆分为 3 个单独的列，然后使用 DATE(Y,M,D) 分配年月和日期列，但失败了，因为我认为它不会将“Sep”识别为月份。我该怎么办？

答案1

有多种解决方案可供选择。

首先，如果 Excel 没有理由相信它是一个日期，它就不会把它当作日期来处理。它是如何相信某个日期的？大致来说，取决于相关计算机上的 WINDOWS 设置。大多数 Windows 设置都包括日期格式，当数据都是数字时，这些格式可以识别常见的排列和分隔符模式，但即便如此，Windows 也会露出丑陋的面目。

注意：Excel 中绝对没有可用于更改此行为的设置。

您可以更改单元格格式，但如果它认为您粘贴了相当于“圣诞老人”的内容，它就不会将其视为日期，也不会触发格式。

例如，我的 Windows 设置将“2021 年 9 月 21 日”理解为日期，但不理解“2021 年 9 月 21 日”。前者无论我是否愿意都会转换为日期，而后者则不会，同样无论我是否愿意。

因此，一种解决方案是更改 Windows 时间格式设置。以及所有电子表格用户的 Windows 时间格式设置。不过我猜这完全行不通。

另一个解决方案是使用 VBA 创建在粘贴到相关单元格时触发的宏。针对每个相关单元格。或者一个在粘贴到任何位置时测试列表中所有单元格的宏。或者一些更聪明但没什么不同的东西。但 VBA 是使用前必须掌握的技能，而且许多组织无论如何都不会允许使用它。

继续前进基本上有两种方法：

  1. 更改粘贴的数据。可以在复制之前更改（因此，请原作者帮您更改），也可以在复制之后，但在将数据呈现到电子表格之前更改。

  2. 按原样粘贴，然后对其进行操作以生成所需格式的日期。

对于第一个问题，“外部来源”就像敲响丧钟一样，在“复制之前”就已存在。但是，对于“复制之后”的部分，您可以做一些笨重且耗时的事情。您可以使用文本编辑器（其中许多文本编辑器都具有完整或类似完整的 RegEx 功能）将数据粘贴并重新排列为 Excel 会将其视为日期的形式。或者在您的电子表格中有一个导入页面，您可以将所有材料转储到该页面中，然后它会取出所需的部分并进行任何重新排列。

哎呀。这不是我的菜。

您可以将其粘贴到现在的任何位置（您似乎愿意这样做），并使用 VBA UDF 对其进行操作，但这会带来“使用 VBA 的技能”和“是否允许使用 VBA”的问题。

那么，也许这是一个不错的公式？您已经知道如何将其拆分并使用这些部分。似乎行不通的是将其重新组合成 Excel 可以正确处理的形式。换句话说，在您完成将其组合在一起的部分之后，如何让它将“Sep”识别为月份？

DATEVALUE()是你的朋友。只需创建字符串并用 包裹该公式DATEVALUE()，它就会转换。嗯，哦，是的……如果 Windows 将该字符串识别为日期……

嗯，有两件事：

  1. 只需将字符串组合成您认为在 Excel 中适用的形式即可。因此，如果“2021 年 9 月 23 日”不起作用，请查看“2021 年 9 月 23 日”是否有效。如果您想尽量减少麻烦，可以通过一些小实验找到可以使用的形式。例如，我发现“2021 年 9 月 21 日”平面不起作用。（“2021 年 9 月 21 日”的欧化版本。）但如果我只是省略逗号，该形式可以很好地工作，既可以作为粘贴的字符串（显然您不能这样做，因为它不是这样！），也可以作为组合的字符串DATEVALUE()。只要不输入逗号，Windows 和 Excel 就会将其视为日期。

  2. 千万不要弄乱“Sep”。粘贴字符串，将其拆开，但将“Sep”部分更改为“9”或“09”。在某个偏僻的地方，或者在最偏僻的地方，一张简单的月份与月份数字的表格就可以了：一个命名范围。有几种方法可以填充命名范围。

但是，比查找表方法更简单的是，即使表格被塞进一个很棒的命名范围（我喜欢这些东西），只需创建一个包含每个月日期的日期列表，使用将TEXT()内部结果数组更改为三个字母的月份缩写，并使用MATCH()或XMATCH()查找与月份匹配的文本“行”。因此，就像这样：

=MATCH("Sep",  TEXT(SEQUENCE(12) & "/01/2021","mmm"),  0)
=MATCH("Sep",  TEXT(ROW(1:12) & "/01/2021","mmm"),  0)

它将创建一个从 1 到 12 的序列，将日期和年份附加到该序列，以便 Excel 意识到这是一个日期列表，然后将其更改为格式化为月份缩写的文本。“Sep”将是列表中的第 9 项，因此查找它将返回“9”。这样您就可以使用“9”，因此您可以使用DATEVALUE()这个 9 和您当前可以提取的日期和年份将各个部分组合在一起，并且 Excel 会根据这些值自行生成日期。您找到的任何顺序都可以。

我保证，如果您尝试找出系统将视为日期的元素顺序，那么直接使用“Sep”创建一个字符串（如前所述）就可以了，但这种方法确实增加了这样的想法，即它效果很好，因为当仅使用数字时，您不会有添加或保留逗号的冲动，并且您熟悉数字元素的顺序肯定会给您一个可识别的日期。所以，无论哪种方式。

或者你也可以直接用这个锤子：

=DATEVALUE( SUBSTITUTE(A1,  ",",  "") )

或者与它非常相似的一个，如果对我来说，逗号有时会产生影响。

相关内容