Parse data into 3 data point (first, middle and last name)

[smith44x]

I am having difficulty parsing approx. 2000 rows of contact name into three separate columns first name, middle name and last name. The data does not have a consistent separator such as a comma etc.

sample data

wendy»Karola¬WINKETT

Vittorio‡halstead

MEADE¬chicky¬¬SEPPEY

kandace¬Tiler

Wyn¬rameaux

CRISTY‡‡Birden

gifford»‡Reymers

janeva»Glenine‡cotherill

WEBB‡Stanlock

Emlynne‡Henriette»¬matchett

YANK‡Zak‡vearncombe

mabel‡ulster

<colgroup><col></colgroup><tbody>
</tbody>

Tried working with magic trick 531 to no avail.

Please let me know your thoughts!

 

[Akuini]

If there are only 2 parts name e.g "Vittorio‡halstead" then column middle name should be blank?
Try this:
Put data in col A then run the code.

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1113731a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1113731-parse-data-into-3-data-point-first-middle-last-name.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] regEx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR], matches [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

va = Range([COLOR=Darkcyan]"A1"[/COLOR], Cells(Rows.count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Brown]3[/COLOR])
       
        [COLOR=Royalblue]Set[/COLOR] regEx = CreateObject([COLOR=Darkcyan]"VBScript.RegExp"[/COLOR])
        [COLOR=Royalblue]With[/COLOR] regEx
            .[COLOR=Royalblue]Global[/COLOR] = True
            .MultiLine = True
            .IgnoreCase = True
            .pattern = [COLOR=Darkcyan]"\w+"[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

    [COLOR=Royalblue]For[/COLOR] i = [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR])
        
        [COLOR=Royalblue]If[/COLOR] regEx.test(va(i, [COLOR=Brown]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]Set[/COLOR] matches = regEx.Execute(va(i, [COLOR=Brown]1[/COLOR]))
            
            vb(i, [COLOR=Brown]1[/COLOR]) = matches([COLOR=Brown]0[/COLOR])
                [COLOR=Royalblue]If[/COLOR] matches.count = [COLOR=Brown]2[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    vb(i, [COLOR=Brown]3[/COLOR]) = matches([COLOR=Brown]1[/COLOR])
                [COLOR=Royalblue]ElseIf[/COLOR] matches.count = [COLOR=Brown]3[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    vb(i, [COLOR=Brown]2[/COLOR]) = matches([COLOR=Brown]1[/COLOR])
                    vb(i, [COLOR=Brown]3[/COLOR]) = matches([COLOR=Brown]2[/COLOR])
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
    [COLOR=Royalblue]Next[/COLOR]

Range([COLOR=Darkcyan]"B1"[/COLOR]).Resize([COLOR=Royalblue]UBound[/COLOR](vb, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]3[/COLOR]) = vb

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[jmacleary]

I would first replace each of the different seperator characters with a single one such as ~. Then carry out a number of replaces to replace ~~ with ~ (keep doing this till no replaces takes place). Finally, use text to columns to seperate out.