Microsoft Excel拆分函数

Question

有人能帮我做下面的代码吗？我目前正致力于将一个单元格值拆分成两列，下面的代码只能分割1行数据，当我试图拆分一个数据的整个列时，它不能工作，并且指示错误代码13并键入不匹配，我尝试过几种方法，比如更改数据类型或它的值根本无法工作。我希望有人能帮我.

Option Explicit

Public Sub NameSplit()
Dim Cell As Range
Set Cell = Range("$A$1")

Dim SplitData() As String
SplitData = Split(Expression:=Cell.Value, Delimiter:="  ") 'double space as delimiter

Dim i As Long, j As Long
For i = LBound(SplitData) To UBound(SplitData)
    If Trim$(SplitData(i)) <> vbNullString Then
        Cell.Offset(ColumnOffset:=j).Value = Trim$(SplitData(i))
        j = j + 1
    End If
Next i
End Sub

Answer 1

分裂柱

• 这是一个基本的示例，它说明了如何循环遍历列范围的单元格，将内容拆分为数组，然后循环遍历数组的元素，将拆分子字符串写入行。

快速修正

Option Explicit

Sub NameSplit()

    Const FirstCellAddress As String = "A2"
    Const ColumnOffset As Long = 1 ' 0 means A2 (overwrite), 1 means B2,...
    
    Const Delimiter As String = "  "
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range
    
    With ws.Range(FirstCellAddress)
        Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' no data in column range
        Set srg = .Resize(lCell.Row - .Row + 1)
    End With
    
    Application.ScreenUpdating = False
    
    Dim sCell As Range
    Dim sArr() As String
    Dim n As Long
    
    For Each sCell In srg.Cells
        sArr = Split(CStr(sCell.Value), Delimiter)
        For n = 0 To UBound(sArr)
            sCell.Offset(, ColumnOffset + n).Value = sArr(n)
        Next n
    Next sCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Data split.", vbInformation

End Sub

Answer 2

试试这个：

Option Explicit


Sub splitName()

Const Delimiter As String = "  "    'double space

Dim rgToSplit As Range
Set rgToSplit = ActiveSheet.Range("A1").CurrentRegion   '---> adjust this to your needs

Dim arrToSplit As Variant
arrToSplit = rgToSplit.Value

Dim i As Long, arrResult As Variant
For i = 1 To UBound(arrToSplit, 1)
    arrResult = Split(arrToSplit(i, 1), Delimiter)
    If UBound(arrResult) > 0 Then
        rgToSplit(i, 1).Offset(, 1).Resize(1, UBound(arrResult) + 1) = arrResult
    End If
Next

End Sub

该代码将要拆分为变量arrToSplit的范围的值。

然后，对每个值进行拆分--将其写入arrResult，并将arrResult写回该行。