With A1 having names try the below formulas...Its a bit lenghty to handle
names without last name...

B1
First name ()
=LEFT(A1,FIND(" ",A1 & " ")-1)

C1
Last name
=IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",99)),99)))

If this post helps click Yes
---------------
Jacob Skaria


"DDay" wrote:

Hi,

I have 4000 line of data which is first and surnames combined e.g DavidHill,
MarkSmith etc etc. I need a formula to divide these by the second capital
letter only to make David Hill, Mark Smith etc etc

All entries have a capital letter at the start of the forename and surname,

thanks