Are there many records? I'd seriously recommend getting the institution to
pull its finger out and give you another dump with tab or column  separation.
They should be ashamed of themselves.

My feeling is that the task of splitting the text  by spaces will be dwarfed
by the task of putting it back together again. The address, particularly,
will be a pain, as there will be variable numbers of words in it depending
on street name, city/county name, floor number, etc.

If there aren't too many records, you might be able to get a reasonable
approximation by a "both ends" approach:

- strip the first two words and use them as the name
- strip the last three words and use them as the city/province/postal
- use the rest as the address

Then go in and fix the 20% that won't fit this.

You might perhaps want to make it a two-step process. If you convert the
strings to comma-separated first and fix that, then you can easily break
them later at the commas into the component parts. It's much easier to move
a comma in a string  than to re-join bits that have been separated wrongly.

So, write a macro to put commas in the appropriate places and return another
string.
Then export the stuff to a decent text editor or Word (Excel is not great
for this).
Then put the commas in the right places and save the file as a text file
with a csv extension.
Then open it back in Excel and Excel will do the comma-to-column split for
you.
Finally, insert a row 1, put in headers, and re-save as a WorkBook.