CODEWITHSUNDEEP
vbaexcel
Hunterhod
Hunterhod

Reputation: 185

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

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.

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.

Upvotes: 2

Views: 30162

Answers (2)

Pinked
Pinked

Reputation: 47

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.

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.

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

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149335

NON VBA Method

Why not use Data~~>Text To Columns?

enter image description here

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

Upvotes: 6

Related Questions