Split Names In One Cell to Multiple Columns

[azzurri13]

I have an output that puts multiple names into one cell. The names are in the format "LASTNAME, FIRSTNAME, LASTNAME2, FIRSTNAME2...)

I need to split the names up and with first and last name separated by a comma, I can't use the delimiter as a comma.

How can I approach this? Any help is appreciated, thanks

 

[Rick Rothstein]

The computer I ran the test on uses an Intel T9300 Dual Core processor @ 2.5GHz.

I am not up on processors for comparison purposes, but Windows tells me my processor is an Intel Core i7-3930K @3.20GHz

 

[JoeMo]

I am not up on processors for comparison purposes, but Windows tells me my processor is an Intel Core i7-3930K @3.20GHz

That should be faster than my old processor.

I just now ran your code (post #4) on this layout (goes to row 10,000):

Excel Workbook
	A
1	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
2	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
3	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
4	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
5	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
6	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
7	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
8	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
9	LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
Sheet1 (8)

Run time (my old computer) is 0.51 seconds, but curiously it doesn't parse out the last of the four names. Result looks like this:

Excel Workbook
	A	B	C
1	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
2	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
3	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
4	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
5	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
6	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
7	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
8	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
9	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
10	LASTNAME1,FIRSTNAME1	LASTNAME2,FIRSTNAME2	LASTNAME3,FIRSTNAME3
Sheet1 (10)

What's up??

 

[Rick Rothstein]

What's up??

The mixture of your commas and comma/spaces revealed an extra space in the Evaluate function argument inside the SUBSTITUTE function call which I thought I had corrected.... that function's second argument is supposed to be "","" and not "", "". Here is the corrected code in full...

Sub SplitFirstLastNamesOutIntoSeparateCells()
  Dim R As Long, C As Long, S As Long, MaxNames As Long
  Dim Rng As Range, Data As Variant, FLnames() As String
  Set Rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  MaxNames = Evaluate(Replace("MAX(0+(LEN(@)-LEN(SUBSTITUTE(@,"","","""")))/2)", "@", Rng.Address))
  Data = Rng.Resize(, MaxNames)
  For R = 1 To UBound(Data)
    S = 0
    Data(R, 1) = Replace(Data(R, 1), ", ", ",")
    For C = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), C, 1) = "," Then
        S = S + 1
        If S Mod 2 = 0 Then Mid(Data(R, 1), C) = Chr(1)
      End If
    Next
    FLnames = Split(Data(R, 1) & Chr(1), Chr(1))
    For C = 1 To MaxNames
      If C <= UBound(FLnames) Then
        Data(R, C) = FLnames(C - 1)
      Else
        Data(R, C) = ""
      End If
    Next
  Next
  Rng.Resize(, MaxNames) = Data
End Sub

 

[István Hirsch]

Are the names separated with a space? Then you can first replace „,_” (comma space) with „ß” then perform Text to Columns with „ß” as a delimiter.

 

[István Hirsch]

Are the names separated with a space? Then you can first replace „,_” (comma space) with „ß” then perform Text to Columns with „ß” as a delimiter.

#24 works if the words are separated as shown in the 1st table of post #22. If there is a comma and a space between each word, give this formula a try:

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&" "&SUBSTITUTE($A1,",","")," ",REPT(" ",600)),2*COLUMNS($A:A)*600,1200))," ",", ")

(enter the formula in B1 then copy down and across)