Split Data in Two Column

g_vanita

New Member
Joined
Jul 29, 2009
Messages
4
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~
 

Some videos you may like

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
Joined
May 9, 2008
Messages
128
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 |.

Hope this can help you!

Rob
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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 {}.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,114
Messages
5,466,760
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top