时间戳

首页 > 解决方案 > 如何让 Excel 在没有手动查找的情况下识别日期并将“/”替换为“/”？

excel - 如何让 Excel 在没有手动查找的情况下识别日期并将“/”替换为“/”？

问题描述

如何让 excel 将时间戳识别为时间戳而不是字符串？

如果我用“/”在“/”上进行查找和替换，它会在大多数文件上修复它：

Cells.Replace What:="/", Replacement:="/", LookAt:=xlPart, SearchOrder :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

我有一段代码通过转换为“逗号”格式并检查单元格是否包含任何“/”字符来检查它是否仍处于错误格式，然后在该实例中触发一个断行以提醒我我需要手动对此文件进行查找和替换。如果我在宏失败时停止宏并手动运行它（Crtl+h，Enter），那么它可以工作，我可以重新启动宏以完成标准化。我需要一种自动化的方法。

我有 >2000 个类似但不完全相同格式的 .csv 文件。每个包含约 350 个变量，每个变量都有自己的时间戳和数据列。我已经编写了一些将其格式化为可用格式的代码。原始 csv 具有“DD/MM/YYYY hh:mm:ss”格式的时间戳，我的计算机和 Excel 默认也是如此。

Excel 似乎随机决定它无法识别大约四分之一的文件时间戳，而是将它们解释为字符串。这可以通过单击单元格然后单击单元格来纠正，然后excel将其识别为时间戳。我需要识别时间戳，以便我可以将值插入标准采样频率，因为 Excel 无法使用它解释为字符串的值进行插值。

每个文件通常有超过 100k 的时间戳，因此手动执行此操作不是一种选择。

我试过使用 SendKeys。问题似乎是它打开了 VBA 脚本编辑器的查找和替换对话框，而不是 Excel 表。我之前尝试过通过调用来转移焦点：

Windows(windowname).Activate
ActiveWorkbook.Application.SendKeys("^h")

我也试过：

Windows(windowname).Application.SendKeys("^h")

这都会导致在 VBA 脚本编辑器上调用查找和替换。

我没有启动宏的快捷方式。

我尝试过 Matlab，但它无法处理文件的标题或填充有文本的列。我想保留所有数据。

我使用宏记录器记录我执行查找和替换的操作，结果如下：

Sub Fixer()
'
' Fixer Macro
'

'
    Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

但是当您在同一个文件上运行它时，这不起作用。

我希望它将字符串“DD/MM/YYYY hh:mm:ss”格式转换为日期时间格式，然后我可以将其转换为十进制格式，然后我可以使用该格式将值内插为可用格式。相反，我没有收到错误消息，也没有任何反应。

原始 CSV 中的日期时间戳示例行是：“31/03/2019 14:55:57,1.0000000149,31/03/2019 14:55:57,14.6,31/03/2019 14:55:57, 57.86,31/03/2019 14:55:57,0.175000000000068"

所以时间戳“31/03/2019 14:55:57”我想转换成“43555.62218750000”

我可以使用脚本来解构字符串，计算十进制等值，然后覆盖单元格，但这会花费非常长的时间。

标签： excelvbareplacefindsendkeys

解决方案

您需要备份一个步骤。

您的问题很常见，并且是由OPEN文件引起的，在这种情况下csv，日期格式 ( DMY) 的格式与您计算机上的 Windows 区域设置的格式不同。

因此，即使看起来可以正确转换的输入，也不会像您所期望的那样交换日和月。

可以说，假设您无法更改csv文件，则此问题的“最佳”修复方法是IMPORT该文件。

根据您执行IMPORT的方式，即取决于 Excel 版本，您将有机会在导入时定义传入数据的日期格式，以便 Excel 正确转换。

在Power Query您可以指定PQ根据指定的语言环境进行日期转换。在早期版本的 Excel 中，将打开一个文本到列向导，允许您指定DMYcsv 文件的格式。我建议使用PQ它，因为它可以轻松处理时间部分，而使用较旧的向导，您需要将两者分开，然后将它们重新添加在一起。

推荐阅读