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

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.

到目前为止我找到的解决方案要求手动选择单元格,这对我正在使用的数据量是不合理的。我找到了这个解决方案,但是我收到以下错误:无效的过程调用或参数。

2 个解决方案

#1

NON VBA Method

非VBA方法

Why not use Data~~>Text To Columns?

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

VBA Method

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

#2

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.

当我在寻找如何拆分列的答案时,我偶然发现了这个问题。我尝试了循环方法但是需要很长时间才能处理。我已经尝试将Text to Columns的字面翻译为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.

在上面的解决方案中,我使用数据(即FirstName和LastName)设置列A,以便将其拆分为范围。在目的地,我将范围放在我希望分割数据出现的位置(即,名字的B列,姓氏的C列)。分隔符是一个空格。它对我来说很好。到目前为止,我已经在2000行数据中测试了代码。

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

我对VBA很新,所以如果代码格式化或编写得不好,请为此道歉。