lastname and firstname

kamakshi · #1 09-20-2013, 04:48 PM

HI

I have column a1 has firstname.lastname@ttc.com
in column b1 should be lastname, firstname
could you please help me this is the code i have written
it comes with lastname@ttc.com firstname as two sepearate columns
and i want only lastname,firstname in one column ( between lastname and firstname include comma

Code:

 
    Dim firstName As String
    Dim lastName As String
    Dim n As Integer
    Dim rowNum As Integer
    Dim colNum As Integer
    rowNum = 1
    colNum = 1
    
    While Cells(rowNum, colNum).Value <> ""
        n = InStr(1, Cells(rowNum, colNum).Value, ".")
        lastName = Left(Cells(rowNum, colNum).Value, n - 1)
        MsgBox (lastName)
        firstName = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) - n)
        MsgBox (firstName)
        
        Cells(rowNum, colNum + 1).Value = firstName
        Cells(rowNum, colNum + 2).Value = lastName
        Cells(1, 3).Value = Cells(1, 3).Value & ", " & Cells(1, 1).Value
        rowNum = rowNum + 1
    Wend