# Split Data in Two Column

#### g_vanita

##### New Member
I have Employee Names such as:

John Violet
Mary jr Dsouza
B n Chandra

I want to split it into two columns, at all places where there is a capital Character.

Such as
John Violet
Mary jr Dsouza
B n Chandra

---------------------
Regards,
~Vani~

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Robby87

##### Board Regular
This is not my personal code, will not take or give credit because I can't remember where it is due, but this will work nicely for your problem.

Assuming the names are in Column A starting at Row 1:

C1: =TRIM(LEFT(A1,MAX(((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91)*ROW(INDIRECT("1:"&LEN(A1)))))-1))

D1: =RIGHT(A1,LEN(A1)-MAX(((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91)*ROW(INDIRECT("1:"&LEN(A1)))))+1)

This formula needs to be entered while holding ctrl+shift (then pressing enter - it's an array formula and you will see { } surrounding it, this step is utterly important, the formula is defunct without it). It will return the names separated by Capital letters, i.e. "John jr Smith" will be returned as | John jr | Smith |.

Rob

#### Weaver

##### Well-known Member
Run this bit of code on a copy of your data

It assumes the names are in column A and that columns B & C are free for the results

Code:
``````Sub capSplit()
firstRow = 2
col = "A"
For Each cel In Range(Cells(firstRow, col), Cells(Rows.Count, col).End(xlUp)).Cells
n = cel.Value
i = 1
Do
i = i + 1
c = Asc(Mid(n, i, 1))
Loop Until i = Len(n) Or (c < 91 And c > 64)
If i < Len(n) Then
cel.Offset(, 1) = Trim(Left(n, i - 1))
cel.Offset(, 2) = Trim(Mid(n, i, Len(n)))
End If
Next
End Sub``````
If you're not sure how to install it, hit alt+F11, choose insert -> module and paste the code in to the main space. Then alt F11 to close this window and alt+F8 to run it.

HTH

#### Weaver

##### Well-known Member
PS love the formulas!

#### Richard Schollar

##### MrExcel MVP
Here's some alternative formulas
Excel Workbook
ABC
1John VioletJohnViolet
2Mary jr DsouzaMary jrDsouza
3B n ChandraB nChandra
Sheet1
Excel 2002
Cell Formulas
RangeFormula
C1=REPLACE(A1,1,LEN(B1),"")
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.