Text to columns to 3 columns

[bobkap]

I have a list of names where both first and last name is separated by a comma. I need to have the last name in one column and the first in another. I think I've figured out how to do that with this code below. Except I cannot figure out how to address two things.
1. If the person's first name is two words (ex.: Billy Joe; or Bobby Sue etc) I end up with 3 columns, not 2. I thought the "consecutive delimeter" would address that but it does not. Might anyone know how to handle that please?
2. Smith, Mary keeps the comma such as "Smith," in the column for the last name. Is there some easy way I can get rid of that comma?

Range(Cells(1, carename), Cells(finalrow, carename)).TextToColumns Destination:=Range("a1"), consecutivedelimiter:=True, DataType:=xlDelimited, Space:=True

Any help would be greatly appreciated!

 

[eduzs]

Try something like:

Dim aStr, carename as string

aStr=Split(carename,",")
Range("DestinationFirstNameCell")=aStr(0)
Range("DestinationLastNameCell")=aStr(ubound(aStr))

 

[bobkap]

Try something like:

Dim aStr, carename as string

aStr=Split(carename,",")
Range("DestinationFirstNameCell")=aStr(0)
Range("DestinationLastNameCell")=aStr(ubound(aStr))

Thanks very much!

 

[eduzs]

You're welcome.