Thread: Text Split
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default 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