Home 
Search 
Today's Posts 
#1




Text Split
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 
#2




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 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 **arrayentered** formulas: To **arrayenter** 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 addin (do a Google search for morefunc to find a good source, and use these formulas: FirstName: =REGEX.MID(A1,"[AZ][az]*",1) LastName: =REGEX.MID(A1,"[AZ][az]*",2) OR you could use a VBA routine. ron 
#3




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 arrayentered, 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 
#4




Text Split
You have more responses at your other post.
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  Dave Peterson 
#5




Text Split
Thanks guys,
really helpful! "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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to split up text  Excel Worksheet Functions  
Split a text  Excel Worksheet Functions  
Split text without using datatext to columns  Excel Discussion (Misc queries)  
Split Text  Excel Discussion (Misc queries)  
text cells end page how split to next. Text lost!  Excel Discussion (Misc queries) 