vba 如何将列中的数据拆分为两个单独的列？

How to split data in a column into two separate columns?

提问by Hunterhod

In Excel, I have a column of names in the format "FirstName LastName". I'd like to split that entire column into two columns, with one containing all of the first names and the other containing all of the last names.

在 Excel 中，我有一列名称格式为“FirstName LastName”。我想将整列分成两列，其中一列包含所有名字，另一列包含所有姓氏。

My code so far:

到目前为止我的代码：

    'Splitting the Traveler Display Name column
    Dim SplitPoint As Long
    'L2 is the column containing names to be split
    Range("L2").Select
    Do Until IsEmpty(ActiveCell)
        'Search for position of space within the cell
        SplitPoint = InStrRev(ActiveCell, " ", -1, vbTextCompare)
        'Put the last name in the column next to the source column
        ActiveCell.Offset(0, 1) = Trim(Left(ActiveCell, SplitPoint))
        'Replace the source column with the first name
        ActiveCell.Offset(0, 0) = Trim(Mid(ActiveCell, SplitPoint))
    Loop

The solutions I have found so far have required that the cells be selected manually, which was unreasonable for the amount of data I am working with. I found this solution, but I get the following error: Invalid Procedure call or argument.

到目前为止，我发现的解决方案要求手动选择单元格，这对于我正在处理的数据量来说是不合理的。我找到了这个解决方案，但出现以下错误：Invalid Procedure call or argument。

回答by Siddharth Rout

NON VBA Method

非 VBA 方法

Why not use Data~~>Text To Columns?

为什么不使用数据~~>文本到列？

VBA Method

VBA方法

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim tmpArray() As String

    '~~> This is the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        LastRow = .Range("L" & .Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            If InStr(1, .Range("L" & i).Value, " ") Then
                tmpArray = Split(.Range("L" & i).Value, " ")
                .Range("M" & i).Value = tmpArray(0)
                .Range("N" & i).Value = tmpArray(1)
            End If
        Next i
    End With
End Sub

回答by Pinked

Private Sub Sample()
    Dim myRng As Range
    Dim LastRow As Long

    LastRow = Sheets("Sample1").UsedRange.Rows.Count

    With Sheets("Sample1")
        Set myRng = Sheets("Sample1").Range("A2:A" & LastRow)
    End With

    myRng.TextToColumns _
      Destination:=Range("B2:C2"), _
      DataType:=xlDelimited, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=True, _
      Other:=False

End Sub

I know that this question is quite old, but sharing an answer for anyone who might encounter the same issue in the future.

我知道这个问题已经很老了，但是为将来可能遇到相同问题的任何人分享一个答案。

I have stumbled across this question as I am searching for answers on how to split a column. I tried the looping method but it takes a long time to process. I have tried the literal translation of the Text to Columns to VBA. The processing time is almost instant, as it is the same as clicking the TextToColumns.

我在寻找有关如何拆分列的答案时偶然发现了这个问题。我尝试了循环方法，但处理时间很长。我已经尝试将文本到列的字面翻译为 VBA。处理时间几乎是即时的，因为它与单击 TextToColumns 相同。

In my solution above, I set the column A with data (i.e., FirstName & LastName) for splitting as a Range. In the Destination, I placed the Range where I want the splitted data to appear (i.e., Column B for First Name, Column C for Last Name). The delimiter is a space. It is working fine for me. So far, I have tested the code in a 2000 rows data.

在我上面的解决方案中，我将列 A 设置为包含数据（即名字和姓氏）以拆分为范围。在 Destination 中，我将 Range 放置在我希望拆分数据出现的位置（即，B 列代表名字，C 列代表姓氏）。分隔符是一个空格。它对我来说很好用。到目前为止，我已经在 2000 行数据中测试了代码。

I am quite new to VBA so apologies if the code might be poorly formatted or written.

我对 VBA 很陌生，所以如果代码格式或编写不当，我深表歉意。