Text Split
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
Another formula that does NOT need to be array-entered, although you still need
to
define seq
to refer to
=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))
FirstName:
=LEFT(A1,LOOKUP(2,1/((CODE(MID(A1,seq+1,1))=65)*
(CODE(MID(A1,seq+1,1))<=90)),seq))
LastName:
=MID(A1,LOOKUP(2,1/((CODE(MID(A1,seq+1,1))=65)*
(CODE(MID(A1,seq+1,1))<=90)),seq+1),255)
--ron
|