Split Names In One Cell to Multiple Columns

azzurri13

New Member
Joined
Mar 29, 2015
Messages
5
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
 
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
 
Upvote 0
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
1LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
2LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
3LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
4LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
5LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
6LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
7LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
8LASTNAME1,FIRSTNAME1, LASTNAME2,FIRSTNAME2, LASTNAME3,FIRSTNAME3, LASTNAME4,FIRSTNAME4
9LASTNAME1,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
ABC
1LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
2LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
3LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
4LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
5LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
6LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
7LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
8LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
9LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
10LASTNAME1,FIRSTNAME1LASTNAME2,FIRSTNAME2LASTNAME3,FIRSTNAME3
Sheet1 (10)


What's up??
 
Upvote 0
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...
Code:
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
 
Upvote 0
Are the names separated with a space? Then you can first replace „,_” (comma space) with „ß” then perform Text to Columns with „ß” as a delimiter.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top