Delete Text After Comma (Excel 2007)

[mcongdon]

I have a list of names in the format LastName, FirstMame. The whole text string is in one column, not separated into two separate columns like I need. Eventually this whole spreadsheet will be imported into Access. Is there a way I can write a script to go into each cell of the column, select the last name before the comma, then copy and paste it into an adjacent empty column. Then do the same with the first names- select the text after the comma, then copy and paste it into a new cell in an adjacent column. I have a working knowledge of how to get around in VBA, but I'm not familiar enough with it to figure out where to start.

Thanks a ton!

 

[dhookom]

I would probably do this in Access but you can create a column in Excel with the expression (assuming A2 is your full name):
=LEFT(A2,FIND(",",A2)-1)
The First name would be:
=TRIM(MID(A2,FIND(",",A2)+1,30))

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

You may find that Data->Text To Columns from the menu will suit.

http://lessthandot.com

 

[mcongdon]

Thanks for the suggestions guys, I ended up finding a way in VB to do it:

Sub Macro1()
    Dim firstName As String
    Dim lastName As String
    Dim n As Integer
    Dim rowNum As Integer
    Dim colNum As Integer
    rowNum = 448
    colNum = 4
    
    While Cells(rowNum, colNum).Value <> ""
        n = InStr(1, Cells(rowNum, colNum).Value, ",")
        lastName = Left(Cells(rowNum, colNum).Value, n - 1)
        firstName = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) - n - 1)
        Cells(rowNum, colNum + 1).Value = firstName
        Cells(rowNum, colNum + 2).Value = lastName
        rowNum = rowNum + 1
    Wend
End Sub

Thanks!