Excel VBA - 将文本转换为日期？ [英] Excel VBA - Convert Text to Date?

问题描述

我有一列日期（列A）以yyyy-mm-dd格式存储为文本，我正在尝试转换为日期，最终可以对它们进行查找。

I have a column of dates (column A) stored as text in the format yyyy-mm-dd which I'm trying to convert to dates, ultimately so that I can do lookups against them.

我已经在这里阅读了几个主题，并尝试了一些建议，但我无法得到任何工作。一个是使用：

I've read a few topics on here and tried some of the suggestions, but I can't get anything to work. One was using:

Columns("A").Select
Selection.NumberFormat = "date"

将单元格的格式更改为迄今，但实际上并未更改仍然存储的值的格式作为文本。

This changed the format of the cells to date but didn't actually change the format of the value which was still stored as text.

我的理解是，我需要使用CDate（）将文本格式的值更改为日期。我已经尝试过这样的东西：

My understanding is that I need to use CDate() to change the format of the value from text to date. I've tried something like this:

Dim c As Range
For Each c In ActiveSheet.UsedRange.columns("A").Cells
    c.Value = CDate(c.Value)
Next c

这给我一个类型不匹配错误。我想知道这是否是因为我正在将日期值保存回到非日期格式的单元格，所以我尝试将其与上面的.NumberFormat =date结合使用，这也不起作用。

Which gives me a type mismatch error. I wondered if this was because I was trying to save date values back into a non-date formatted cell so I tried combining it with the .NumberFormat = "date" above, which didn't work either.

任何建议将不胜感激。

推荐答案

您可以快速转换类似日期的文本列使用VBA等效于工作表的数据►文本到列命令的实际日期。

You can quickly convert a column of text that resembles dates into actual dates with the VBA equivalent of the worksheet's Data ► Text-to-Columns command.

With ActiveSheet.UsedRange.Columns("A").Cells
    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
    .NumberFormat = "yyyy-mm-dd"   'change to any date-based number format you prefer the cells to display
End With

批量操作通常比循环单元更快，VBA的 Range.TextToColumns方法非常快。它还允许您自由设置MDY与DMY或YMD转换掩码，使掩盖许多文本导入，其中日期格式与系统的区域设置不匹配。有关可用的完整列表，请参阅 TextFileColumnDataTypes属性日期格式可用。

Bulk operations are generally much quicker than looping through cells and the VBA's Range.TextToColumns method is very quick. It also allows you the freedom to set a MDY vs. DMY or YMD conversion mask that plagues many text imports where the date format does not match the system's regional settings. See TextFileColumnDataTypes property for a complete list of the available date formats available.

注意事项：导入日期的某些的文本尚未被转换时要小心。具有不明确的月和日整数的基于文本的日期可能已被错误地转换;例如07/11/2015可能被解释为2015年11月07日或2015年7月11日，具体取决于系统区域设置。在这种情况下，放弃导入并将文本重新插入数据►从文本中获取外部数据►在文本导入向导中指定正确的日期转换掩码。在VBA中，使用 Workbooks.OpenText方法并指定xlColumnDataType。

Caveat: Be careful when importing text that some of the dates have not already been converted. A text-based date with ambiguous month and day integers may already been converted wrongly; e.g. 07/11/2015 may have been interpreted as 07-Nov-2015 or 11-Jul-2015 depending upon system regional settings. In cases like this, abandon the import and bring the text back in with Data ► Get External Data ► From Text and specify the correct date conversion mask in the Text Import wizard. In VBA, use the Workbooks.OpenText method and specify the xlColumnDataType.

这篇关于Excel VBA - 将文本转换为日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！