Formula to split text after block capitals

hblbs

Board Regular
Joined
Mar 18, 2009
Messages
184
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
Thank you, this seems to work, I have modified slightly and input the following.

in E2 I have added

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

To get the surname in in F2 I use

Code:
=PROPER(LEFT(A2,E2))

and for the forename in G2 I use

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

Many thanks for your input.

*****SOLVED*********
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top