There is a small set of custom text functions which allow you to split text based on different delimiters, and the delimiters can be counted from the beginning or the end of the text. As delimiters can be any text, you can extract a word and then use it as a delimiter in the next function.
For example in cell B2 is the name Alexander G. Bell
in C2 is =xtndleft(B2," ",1)
in E2 is =xtndright(B2," ",-1)
sandwiched in the middle in cell D2 is =xtndmid(B2,C2,1,E2,1)
which uses the extracted first and last names to get anything inbetween
Here is the output for three names, one with a middle initial, one without and one with a full middle name.
The formulas on row 2 were just dragged down. the same formulas worked for all three name variations.
B C D E
2 Alexander G. Bell Alexander G. Bell
3 Alexander Bell Alexander Bell
4 Alexander Graham Bell Alexander Graham Bell
These text functions are UDF's (user defined functions) and have to be entered into a visual basic module, but it may be worth the effort, rather than doing the separation manually.
These formulas should work for the address issue, especially if city, state and zip are present for all addresses.
1776 W State St Boise Idaho 64919 in cell B5 can be split into:
C D E F
5 1776 W State St Boise Idaho 64919
with the following formulas
D5 =xtndmid(B5," ",-3," ",-2)
E5 =xtndmid(B5," ",-2," ",-1)
F5 =xtndright(B5," ",-1)
The formulas work by getting text starting at the end using the spaces and once 'Boise' was found, the street address was just everything to the left of 'Boise'
Once you have entered the four formulas just drag them down as usual for further addresses
These functions are free and can be found here.