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

This is a variation of a previous question solved by Sergei Baklan posted here: https://techcommunity.microsoft.com/t5/Excel/extract-player-names-following-text-string-references/m-p/1039852#M45460

Mr. Baklan came up with a clever way to extract text following certain text references. Each text reference was different from the other.

In this example, we have duplicate text references referring to more than one player. Here is the example of data found in one cell:

QB Drew Brees RB Alvin Kamara FLEX Leonard Fournette RB Brian Hill WR DJ Moore WR Jarvis Landry WR Allen Hurns TE Jared Cook DST Broncos

In this case, RB refers to two different players -- Alvin Kamara and Brian Hill.

How do I adjust Sergei Baklan's work to take into account identical text referring to two different players?

In the attached example, see the input in Column B and the output in Columns D:L. The formula is not properly returning data in Columns F, H, and I.

Please see the attached.

Separate-NFL-DFS-player-data-into-columns.xlsx63 KB

excel

Formulas and Functions

SergeiBaklan

Dec 02, 2019

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.

Forum Discussion

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

16 Replies

Resources