One way of identifying the first and last names in a cell is to use text to columns function and delimit the cells in the column where the name is, by space. This is great in circumstances where only the first name and surname are in the cell to begin with. Once we start having cells where middle names (sometimes several) the data will split out well, but it will be in 3,4,5 or even more columns.
If you’re dealing with a lot of rows of data, to go down 1 by one and put the last found value in a row into the 2nd column where you may want the surname to be it would be a very manual task.
The code example below is very quick and essentially grabs the outermost value in the first 15 rows and brings it in to the 2nd column in, giving you your first name and surname, and deleting the superfluous data from columns 3 to 15. The parameters can be changed to suit your specific needs, but feel free to leave a message or get in contact with us to help on your Excel project or objective.
For r = 2 To 10000
For c = 15 To 3 Step -1
If ActiveSheet.Cells(r, c) <> "" Then
ActiveSheet.Cells(r, c - 1) = ActiveSheet.Cells(r, c)
ActiveSheet.Cells(r, c) = ""