防止Excel将数字范围转换为日期格式

I'm using Excel to translate data from one system which outputs .csv files to another which can read in .xls files. One column is sizes, which frequently includes terms like 4-6, 6-8, etc. When the csv is opened in Excel, these are automatically(?) converted to dates (6-Apr, 8-Jun, etc.) I can use .NumberFormat = "m-d" to get the data to *look like it should, but of course it's still a date value, and any operations I try to perform with it convert back to date. Is there any way to convert it to text in exactly that format? If I declare the .NumberFormat as "@", it just changes the value to a serial. I use the size, along with several other columns to create one long product ID code for inventory purposes.... when the size changes to a date value, I end up missing some of the data.

我正在使用Excel将数据从一个输出.csv文件的系统转换为另一个可以读入.xls文件的系统。一列是大小，通常包括4-6,6-8等术语。当在Excel中打开csv时，它们会自动（？）转换为日期（6月4日，8月6日等）I可以使用.NumberFormat =“md”来获取数据*看起来应该如此，但当然它仍然是一个日期值，我尝试用它执行的任何操作都会转换回日期。有没有办法将其转换为完全相同格式的文本？如果我将.NumberFormat声明为“@”，它只是将值更改为序列。我使用大小以及其他几个列来创建一个长的产品ID代码用于库存目的....当大小更改为日期值时，我最终错过了一些数据。

1 个解决方案

#1

I found a solution to this problem that runs entirely from VBA - i.e., doesn't depend on special import conditions. I would appreciate any pointers or recommendations, especially if this has the potential to create unforeseen errors:

我找到了完全从VBA运行的这个问题的解决方案 - 即，不依赖于特殊的导入条件。我会很感激任何指针或建议，特别是如果这有可能产生无法预料的错误：

Dim Size as String
For i = 2 To LastRow
    If IsDate(Range("H" & i).Value) Then
        Size = Month(Range("H" & i)) & "-" & Day(Range("H" & i))
        Range("H" & i).NumberFormat = "@"
        Range("H" & i) = Size
    End If
Next i

The column I'm testing includes size values that are string (XL, SM, 12T, etc), along with values that get converted to dates (4-6, 6-8). This seems to convert it back to a string value, and then properly treats it as a string value on all future uses (concatenations and logical tests)

我正在测试的列包括字符串（XL，SM，12T等）的大小值，以及转换为日期（4-6,6-8）的值。这似乎将其转换回字符串值，然后在将来的所有使用（连接和逻辑测试）中将其正确地视为字符串值