Text Split

[DDay]

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

 

[Mike H]

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(LOWER(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

 

[Bob Phillips]

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

 

[Jacob Skaria]

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]

I misunderstood the query..Please ignore this post...

 

[Roger Govier]

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