不可能在Range.TextToColumns中抑制字符串的自动转换？

Question

TextToColumns对象的方法会自动将字符串转换为数字，但是最好能抑制这个特性。该方法有一个TextQualifier参数，但它似乎没有完成我正在寻找的任务。以下数据说明了这一问题。列A中的字符串用分号分隔，分号将文本部分与数字部分分隔开来。请注意，数字都以零开头，第4-6行中的数字以撇号作为前缀：

Column A
StringRow1;01000
StringRow2;02000
StringRow3;03000
StringRow4;'01000
StringRow5;'02000
StringRow6;'03000

以下宏将字符串拆分为B列中的文本部分和C列中的数字部分。

Sub TTC()
    Application.DisplayAlerts = False
    Dim rToSplit As Range
    Set rToSplit = ThisWorkbook.Worksheets(1).Range("A1:A6")
    rToSplit.TextToColumns _
        Destination:=Range("B1"), _
        DataType:=xlDelimited, _
        Semicolon:=True, _
        TextQualifier:=xlTextQualifierNone
End Sub

最后一列说明了所需的输出：

Column A               Column B           Column C
Data                   Output             Output             Desired Output
StringRow1;01000       StringRow1            1000                 01000
StringRow2;02000       StringRow2            2000                 02000
StringRow3;03000       StringRow3            3000                 03000
StringRow4;'01000      StringRow4          '01000                 01000
StringRow5;'02000      StringRow5          '02000                 02000
StringRow6;'03000      StringRow6          '03000                 03000

在拆分之前，我尝试过格式化列C，如下所示：

rToSplit.Offset(, 3).NumberFormat = "@"，但它没有效果。将TextQualifier参数切换到xlTextQualifierSingleQuote具有处理行4-6与行1-3相同的效果。

我是在要求不可能的东西吗？或者是否存在一些我不知道的应用程序级别设置？或者我能用A列中的字符串做些聪明的事吗？

(当然，我可以在拆分后遍历C列并修复这个问题，但是对于大型数据集，这是没有吸引力的。对于我的特定应用程序，A列中的字符串是由代码生成的，这些代码在几个不同的文本文件中搜索数以万计的文本行中的模式；每个匹配项都添加到字典中，然后使用array() = Dictionary.Items和DestinationRange.Value = Application.WorksheetFunction.Transpose(array)将数据读入工作表。这太快了。解决这里描述的问题的方法是将数字字符串保存在一个单独的字典中，该字典在拆分后读入C列。这很好，所以我出于好奇发布了这篇文章，想看看我能学到什么。)

Answer 1

可以使用FieldInfo属性为每一列设置数据类型。您需要知道预先有多少列，或者知道哪一列将包含数字。

FieldInfo参数接受一个数组，每个子数组都有两个值。第一个值表示列号(从1开始)，第二个数字是您希望将该列格式化为XLColumnDataType。

在本例中，您希望将所有内容格式化为文本(而不是像当前这样的数字)，因此您将使用xlTextFormat (这只是一个定义为2的系统常量)。

x.TextToColumns _
    Destination:=Range("B1"), _
    DataType:=xlDelimited, _
    Semicolon:=True, _
    TextQualifier:=xlTextQualifierNone, _
    FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat)) 'Format columns 1 and 2 as text

Answer 2

如果你愿意用循环来做这件事：

Sub TTC()

    Dim row As Long, lastRow As Long, splitSpot As Integer, cellValue As String

    Application.ScreenUpdating = False    

    With ThisWorkbook.Worksheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        For row = 1 To lastRow
           cellValue = CStr(.Range("A" & row).Value)
           splitSpot = InStr(cellValue, ";")
           .Range("B" & row & ":C" & row).NumberFormat = "@"
           .Range("B" & row).Value = Left(cellValue, splitSpot - 1)
           If Mid(cellValue, splitSpot, 1) = "'" Then
               .Range("C" & row).Value = Right(cellValue, Len(cellValue) - splitSpot + 1)
           Else
               .Range("C" & row).Value = Right(cellValue, Len(cellValue) - splitSpot)
           End If
        Next
    End With

End Sub

ss：

​

​