Formula to split text after block capitals

[hblbs]

Hi all, hope you can help.

In column E, I have Surname/s in block capitals followed by a space and then Forename/s with capital first letter and lower case the remainder.

The question is there a way to separate Surname/s in one column with the Forename/s in a second column?

I can use text to columns with the space option, but this splits everything with a space including where people have double barrelled surnames (without the hyphen) and multiple forenames.

Any help is appreciated, thanks in advance.

 

[paulxmw]

.

 

[RoryA]

This array formula will give the position of the Capital letter preceding the first non-capital letter:

=MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90,0)-1

Note that it has to be entered using Ctrl+Shift+Enter. You can then use LEFT and MID functions to extract the text before/after that point as required.

 

[hblbs]

Thank you, this seems to work, I have modified slightly and input the following.

in E2 I have added

{=MATCH(TRUE,CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>90,0)-3}

To get the surname in in F2 I use

=PROPER(LEFT(A2,E2))

and for the forename in G2 I use

=MID(A2,E2+2,LEN(A2)-E2)

Many thanks for your input.

*****SOLVED*********