Splitting a column based on text CASE

[n_c_manning]

I currently have a column that contains both First and Last Names. Some first names have multipal names and some surnames have multipal names. Below is some sample data:
Jean Michael BURGES
Bill HARRIS
Willow VAN GRATH
Lisa jane VAN DE BERG

I need a way to get a column that contains the First Name (not ALL CAPS) and a column for the Surname (which is identifyed by the ALL CAPS)

I have tried making some functions based on what I have read in previous threads without any luck. I have also found them hard to debug. Any help is greatly appreciated.

 

[Benjaminmin]

Hi,

If you look at the post here http://www.mrexcel.com/forum/showthread.php?t=557367 there might be some help.

It involves using a =MATCH(EXACT function.

Hope it helps a little

 

[n_c_manning]

Thanks Benjaminmin, If I get stuck I will play with this in a function. Been finding the functions very hard to debug so will hold out for now and hope that someone can give me some code I can cut and paste.
Fingers crossed.