ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Text Split (https://www.excelbanter.com/new-users-excel/230811-text-split.html)

DDay

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

Ron Rosenfeld

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

Ron Rosenfeld

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

Dave Peterson

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

DDay

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



All times are GMT +1. The time now is 01:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com