#1   Report Post  
Old May 14th 09, 11:33 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 3
Default 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   Report Post  
Old May 14th 09, 12:00 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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


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   Report Post  
Old May 14th 09, 12:06 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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
  #4   Report Post  
Old May 14th 09, 12:53 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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   Report Post  
Old May 14th 09, 02:39 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to split up text Hilton Excel Worksheet Functions 5 July 10th 08 11:50 AM
Split a text Manos Excel Worksheet Functions 7 October 17th 07 05:51 PM
Split text without using data-text to columns Jambruins Excel Discussion (Misc queries) 7 January 21st 06 02:16 PM
Split Text Gabe Excel Discussion (Misc queries) 6 January 4th 06 09:52 PM
text cells end page how split to next. Text lost! Elaine Excel Discussion (Misc queries) 1 August 28th 05 05:48 PM


All times are GMT +1. The time now is 01:50 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017