This isn't a bad start, kamakshi. Let's analyze what the program is actually doing, and maybe that'll help you understand how to change it:
Code:
n = InStr(1, Cells(rowNum, colNum).Value, ".")
lastName = Left(Cells(rowNum, colNum).Value, n - 1)
Perfect. Well, almost perfect; you now have the first name in a variable named "lastName". I won't insult your intelligence by explaining how to fix that part.
Code:
firstName = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) - n)
Not bad. Now you have the remainder of that email address in a value called firstName. The remainder, unfortunately, isn't the first name, nor even the last name; it's the rest of the email address, "
lastname@ttc.com" in your example. Do you see why?
Code:
Cells(rowNum, colNum + 1).Value = firstName
Cells(rowNum, colNum + 2).Value = lastName
Now you've put those two values in two cells to the right, the firstName variable (which contains the remainder of the email address) in the first cell to the right, and lastName (which contains the first name) in the cell after that.
This is exactly what you said is happening, and maybe you already understood
why it's happening that way.
Code:
Cells(1, 3).Value = Cells(1, 3).Value & ", " & Cells(1, 1).Value
I don't know why this statement is in there. It seems to concatenate A1 to the end of C1 (with a comma between them), and to do that over and over for every row you're trying to handle. So not only will most of your rows end up as "
lastname@ttc.com" and "
firstname", but the first row (only) will end up as "
lastname@ttc.com" and "
addr,firstname,firstname,firstname....". I'm guessing you put that in there while you were experimenting, and forgot to take it out.
Code:
rowNum = rowNum + 1
This is, again, perfect; you're getting ready to look at the next row.
What you need, here, is a) to change the name of that first variable to "firstName" (because that's what you're putting in it), and b) to figure out how to extract lastName from "
lastname@ttc.com". Hint: You already did something very like it in the first step, figuring out where the '.' was and taking everything to the left of it. Now you want everything to the left of the '@'.....