查找/替换正确格式化日期单元格的宏 [英] Find/replace macro that properly formats a date cell

问题描述

我正在使用将某些数据导出为CSV格式的应用程序.但是，日期列在原始数据中的格式不正确，并且包含年份中不必要的尾随字符，这会导致Excel导入后无法正确地解释日期.

I'm using an app that exports certain data to a CSV format. The date column, however, is not properly format in the raw data and contains unnecessary trailing characters for the year, which prevents Excel from interpreting it correctly once imported.

如果我在列上手动运行查找和替换"，则这些值将自动识别为日期.但是，如果我将操作记录为宏，然后再次运行，则会删除尾随字符，但该列中的数据将保留为文本，而不是日期.

If I manually run Find and Replace on the column, the values are automatically recognized as dates. If I record the operation as a macro, however, and run it again, the trailing character is removed, but the data in the column is retained as a text, not as a date.

这是VBA代码:

Sub formatDate()

Columns("A:A").Select
Selection.Replace What:=" г.", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

日期的区域格式为DD.MM.YYYY.另外，如果我仅编辑任何结果单元格(F2)并按Enter键而不进行其他操作，则Excel会切换为正确的格式.

The regional format for a date is DD.MM.YYYY. Also, if I simply edit any of the resulting cells (F2) and hit Enter without anything else, Excel switches to the proper format.

样本数据:

4 март 2017 г.
4 март 2017 г.
3 март 2017 г.
1 март 2017 г.
28 февруари 2017 г.
27 февруари 2017 г.
27 февруари 2017 г.
26 февруари 2017 г.
26 февруари 2017 г.

推荐答案

使用TextToColumns快速删除结尾的字符并转换为日期.

Use TextToColumns to quickly strip off the trailing characters and convert to dates.

使用固定的dd.mm.yyyy格式的类似日期的文本，

With the text-that-look-like-dates in a fixed dd.mm.yyyy format,

with selection
    .TextToColumns Destination:=.cells(1, 1), DataType:=xlFixedWidth, _
                   FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))
    .numberformat = "dd.mm.yyyy"
end with

类似日期的文本在日期之后和尾随文本之前显示一个空格，

With the text-that-look-like-dates showing a space after the date and before the trailing text,

With Selection
    .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
                   Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
                   FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlSkipColumn))
End With

如果TextToColumns给您提供保加利亚月份名称的问题(VBA非常以US-EN为中心)，则只需在现有代码中添加一行，然后选择设置单元格格式即可.

If the TextToColumns is giving you problems with the Bulgarian month names (VBA is very US-EN-centric), then simply add a single line to your existing code and optionally set the cell format.

with Selection
    .Replace What:=" г.", Replacement:=vbNullString, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
    .value = .value2
    .numberformat = "dd.mm.yyyy"
end with

我在这里使用了Selection(不是特别推荐)，但您应该可以轻松进行转化.

I've used Selection here (not particularly recommended) but you should be able to easily convert.

这篇关于查找/替换正确格式化日期单元格的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！