Split Data in Two Column

[g_vanita]

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~

 

[Robby87]

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]

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

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]

PS love the formulas!

 

[Richard Schollar]

Here's some alternative formulas

Excel Workbook
	A	B	C
1	John Violet	John	Violet
2	Mary jr Dsouza	Mary jr	Dsouza
3	B n Chandra	B n	Chandra
Sheet1

Excel 2002

Cell Formulas
Range	Formula
C1	=REPLACE(A1,1,LEN(B1),"")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.