Split "Lastname, Firstname Middlename" 1

[kstargold]

I have a listing of people that are in program at my university. The cells of the Excell sheet are formated "Lastname, Firstname Middlename". I'm working on doing a mail merge, so I need them in "Firstname Middlename Lastname" format.

I have learned a few things about spilt, but I don't know how to reorder the cell. This is what I have:

Private Sub CommandButton1_Click()
Dim Name As String
Dim lName As String
Dim fName As String
Dim i As Integer
i = 2

While Cells(i, "a").Value >= "0"
    Name = Cells(i, "b").Value
   'lName = Split(Name, ",")
    i = i + 1
Wend
End Sub

I need some way of deleting "len(lName)+ 2" (+ 2 for ", ") spaces from the front of the cell, but I don't know how to do that.

Any ideas?

Thanks,
Kanan

 

[PHV]

Something like this ?
Private Sub CommandButton1_Click()
Dim i As Integer, a
i = 2
While Cells(i, "a").Value >= "0"
a = Split(Cells(i, "b").Value, ",")
Cells(1, "c") = Trim(a(1)) & " " & Trim(a(0))
i = i + 1
Wend
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kstargold]

That worked great, thanks!!!