I am using the below formula to separate First and Last names from one column into two because the Text to Columns isn't working either for some reason. However, this formula only seems to be working for the first two rows and I get #VALUE! when I attempt to copy it down. Any help is much appreciated.

=LEFT(A1,FIND(" ",A1)-1)