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

Hi,

Lets say we have MarkSmith in A1.

Put this array formula in B1 to extract the first name. Note it's all one
line and see below for how to enter and array formula

=LEFT(A1,MATCH(0,TRANSPOSE(--EXACT(MID(A1,ROW(INDIRECT(2&":"&LEN(A1))),1),MID(L OWER(A1),ROW(INDIRECT(2&":"&LEN(A1))),1))),0))&""

And then this in c1 to get the second name

=MID(A1,LEN(B1)+1,LEN(A1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike



"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

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

For j = Len(.Cells(i, TEST_COLUMN).Value) To 2 Step -1

If Asc(Mid$(.Cells(i, TEST_COLUMN).Value, j, 1)) = 90 Then

.Cells(i, TEST_COLUMN).Value = _
Left$(.Cells(i, TEST_COLUMN).Value, j - 1) & _
" " & Mid$(.Cells(i, TEST_COLUMN).Value, j)
End If
Next j
Next i
End With

End Sub

--
__________________________________
HTH

Bob

"DDay" wrote in message
...
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

With A1 having names try the below formulas...Its a bit lenghty to handle
names without last name...

B1
First name ()
=LEFT(A1,FIND(" ",A1 & " ")-1)

C1
Last name
=IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",99)),99)))

If this post helps click Yes
---------------
Jacob Skaria


"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

I misunderstood the query..Please ignore this post...
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With A1 having names try the below formulas...Its a bit lenghty to handle
names without last name...

B1
First name ()
=LEFT(A1,FIND(" ",A1 & " ")-1)

C1
Last name
=IF(ISERROR(FIND(" ",A1)),"",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",99)),99)))

If this post helps click Yes
---------------
Jacob Skaria


"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

Also look at the responses you have got to your posting in another
newsgroup.

--
Regards
Roger Govier

"DDay" wrote in message
...
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