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