Split First Name and Last Name

First formula contain 2 function.
FIND - determines the position of your delimiter
LEFT - returns the character from start upto the delimiter

2nd function
SUBSTITUTE - removes the first name from your complete name list

Highlight the column with the data you want to SPLIT
Go to DATA > Text to Column

Choose Delimited then click next.

Choose your delimiter, in my example, its SPACE, then click next.

Choose your desired format and choose your destination cell.
You just need to highlight the first cell where the new data will start (eg =D6).

*For multiple first or last name, it will be separated accordingly.