search

Split first and last names in Excel VBA


When data is formatted like this:

Colbert, Mike test@test.com BAIS IS

And you want it like this:

Mike Colbert test@test.com BAIS IS

Sub dataCleanUp()

Dim name As String
Dim email As String
Dim major As String
Dim track As String

Dim i As Integer   ' loop counter
Dim nStudents As Integer ' row counter

Dim fName As String
Dim lName As String

Dim fullName() As String
Dim j As Integer 'array loop counter

nStudents = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count

    For i = 0 To nStudents - 1

        'Read in the data and populate variables for each row
        name = Range("A1").Offset(i, 0).Value
        email = Range("B1").Offset(i, 0).Value
        major = Range("C1").Offset(i, 0).Value
        track = Range("D1").Offset(i, 0).Value

        'split the fullName into first and last names
        fullName = Split(name, ",")  'split on the comma
                                        'Chr(32) is a space

        'after they're split, the values are put in an array
        Range("H1").Offset(i, 0).Value = fullName(0) 'last name comes first
        Range("G1").Offset(i, 0).Value = fullName(1) 'then first name


        Range("I1").Offset(i, 0).Value = name
        Range("J1").Offset(i, 0).Value = email
        Range("K1").Offset(i, 0).Value = major
        Range("L1").Offset(i, 0).Value = track

        'Remove the two spaces from the beginning of the first name
        fName = Range("G1").Offset(i, 0).Value
        Range("G1").Offset(i, 0).Value = Right(fName, Len(fName) - 2)


    Next i


End Sub

close
search

Hi, guest!

settings

menu