Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 **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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 data-text 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) |