Extract different player names referring to an identical text string into separate columns.

steveccc 

Let transform identifiers such way

i.e. <space><identifier><space><sequential number of identifier>

When formula in D2 could be

=IFERROR(MID($B2,
    SEARCH( REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
            SUBSTITUTE( " " & $B2,
               LEFT(D$1,LEN(D$1)-1),
               REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
               RIGHT(D$1))
      ) + LEN(D$1)-2,
      IFERROR(
         AGGREGATE(15,6,
            1/( SEARCH( REPT("@",LEN(LEFT($D$1:$L$1,LEN($D$1:$L$1)-1))),
                        SUBSTITUTE( " " & $B2,
                           LEFT($D$1:$L$1,LEN($D$1:$L$1)-1),
                           REPT("@",LEN(LEFT($D$1:$L$1,LEN($D$1:$L$1)-1))),
                           RIGHT($D$1:$L$1)
                        )
               )-1 >
               SEARCH(  REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
                        SUBSTITUTE( " " & $B2,
                           LEFT(D$1,LEN(D$1)-1),
                           REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
                           RIGHT(D$1)
                        )
               )  )*
               SEARCH(  REPT("@",LEN(LEFT($D$1:$L$1,LEN($D$1:$L$1)-1))),
                        SUBSTITUTE( " " & $B2,
                           LEFT($D$1:$L$1,LEN($D$1:$L$1)-1),
                           REPT("@",LEN(LEFT($D$1:$L$1,LEN($D$1:$L$1)-1))),
                           RIGHT($D$1:$L$1)
                        )
               ),1
         ),
      LEN($B2)+1
      ) -
       SEARCH( REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
               SUBSTITUTE( " " & $B2,
                  LEFT(D$1,LEN(D$1)-1),
                  REPT("@",LEN(LEFT(D$1,LEN(D$1)-1))),
                  RIGHT(D$1)
               )
       ) - LEN(D$1)+1
),"")

when drag to the right and down (or better use Ctrl+D).

Perhaps formula could be optimized, but it will take time to play with it and find another way if any. Right now it takes few minutes to fill couple hundred thousand rows. Thus better to test on few ones.

That is in second sheet of the attached file.

UPDATE: I failed to upload the file, will try once more tomorrow.