On Thu, 14 May 2009 03:33:01 -0700, 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

Define a name like seq referring to the formula (Using the Define Name menu
option):

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

With your namestring in A1, you can use these **array-entered** formulas:

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

FirstName:

=LEFT(A1,MATCH(TRUE,ISNUMBER(1/((CODE(MID(A1,seq+1,1))=65)*
(CODE(MID(A1,seq+1,1))<=90))),0))

LastName:

=MID(A1,MATCH(TRUE,ISNUMBER(1/((CODE(MID(A1,seq+1,1))=65)*
(CODE(MID(A1,seq+1,1))<=90))),0)+1,255)

Or you could download and install Longre's free morefunc.xll add-in (do a
Google search for morefunc to find a good source, and use these formulas:

FirstName: =REGEX.MID(A1,"[A-Z][a-z]*",1)
LastName: =REGEX.MID(A1,"[A-Z][a-z]*",2)

OR you could use a VBA routine.
--ron