lastname and firstname

[kamakshi]

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

 
    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

[BobBridges]

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:

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.

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?

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.

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.

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 '@'.....

[kamakshi]

Thanks i want everyhting in the left of the @

[Pecoflyer]

A non VBA solution:

Select the column
1st step

• Ctrl+H
• Replace what @*
• With ( leave blank)
• Replace all

2nd step

• Ctrl+H
• replace what .
• With ,
• Replace all

There most certainly is a way to translate this to VBA without the necessity of loops

[kamakshi]

Hi
Thanks and i want lastname, firstname
it shows like firstname,lastname

[BobBridges]

Kamakshi, I won't speak for anyone else here, but if it's left up to me the only kind of help you'll get is the kind that helps you think about this until you understand it and can solve it yourself. So far as I can tell from your last two posts, you're not thinking; you're just half-reading and not trying very hard to understand.

Others may disagree with me and just give you a formula. But I think you should read what Pecoflyer and I have written, and ask questions about it, and/or explain a) what you understand about it and b) what you don't understand.

Actually you don't have to read both our posts. Peco's right, you don't need a VBA program to do this. I just answered in terms of VBA because that's how you phrased the question; but you can do it with worksheet functions instead.