How to seperate lastname and firstname?

[kai]

Hi, All
I have a column "Name", it has full name, format "lastname, firstname". I
would like to write code to split into "Lastname" and "Firstname" column.
How do write code to seperate lastname when I see ","?

Thanks

kai

 

[KL]

Hi kai,

First of all, this task can be performed by simple formulae:

last name =LEFT(TRIM(A1),FIND(",",TRIM(A1))-1)
first name =MID(TRIM(A1),FIND(",",TRIM(A1))+2,LEN(TRIM(A1)))

or via the menu Data>Text to column... etc.

If you still want to use the code and you use XL2000 or later you could try
this:


Sub Test()
With ActiveSheet
Set rng = .Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With
For Each c In rng
If c Like "*,*" Then
myNames = Split(c, ",")
c.Offset(, 1) = Trim(myNames(0))
c.Offset(, 2) = Trim(myNames(1))
End If
Next
End Sub



Regards,
KL

 

[KL]

Actually, the formulae can be simplified:

last name =TRIM(LEFT(A1,FIND(",",A1)-1))
first name =TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))

Regards,
KL

 

[kai]

KL,
Thanks a lot, it works like magic.

Kai

Actually, the formulae can be simplified:

last name =TRIM(LEFT(A1,FIND(",",A1)-1))
first name =TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))

Regards,
KL

 

[Gary Keramidas]

i know you wanted to write code to do this, but you can also use the text to
columns menu item
select column
click data/text to columns
select delimited and next
click the box next to comma click finish
you will have the names in separate columns.

you want code, just record a macro while you're doing this operation.