Forum Discussion

steveccc's avatar
steveccc
Brass Contributor
Dec 01, 2019
Solved

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 ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    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.

steveccc's avatar
steveccc
Brass Contributor
Dec 02, 2019

SergeiBaklan 

Hi Sergei. It seems like you are using NBA (basketball) player data as an example. But for this question, we should be using the NFL (football) player instead (please see attached). 

Your original formula solution for NBA player data worked because each player was referenced by a unique identifier. 

However, your formula solution for NFL player data does not work because the NFL data uses shared identifiers for each position.

 

Let's look at the data in Cell B2 in the attached example of NFL player data:
QB Carson Wentz RB Christian McCaffrey RB Miles Sanders WR Davante Adams WR DeVante Parker FLEX Alshon Jeffery WR Dede Westbrook TE Tyler Higbee DST Chiefs

On the Excel worksheet, Identifiers are in D1:L1
Here is how the data appears in D2:L2 using your formulas: 
Carson Wentz R Christian McCaffrey RB Miles Sanders W Christian McCaffrey RB Miles Sanders W Davant Davant Davant Adams WR DeVante Parker F Alshon Jeffery WR Dede Westbrook TE Tyler Higbee D Chiefs

 

Obviously this is not working properly. However, if there was a way to change the Column B data so that the shared identifiers (RB, RB, WR, WR, WR) are changed into unique names (RB 1, RB2, WR1, WR2, WR3), then your original formula solution would work.

 

In summary, some kind of find/replace solution is needed for transforming the shared identifiers (RB, RB, WR, WR, WR) in Column B into unique identifiers (RB1, RB2, WR1, WR2, WR3). 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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.

  • steveccc's avatar
    steveccc
    Brass Contributor
    Dec 03, 2019

    SergeiBaklan Wow, this is great! Thanks very much. I took your instructions and updated the Excel file on my own (attached). While it did take a few minutes to complete on my desktop, it is a much preferred solution to anything I could do before. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 03, 2019

      steveccc 

      My previous file was more than 75MB which is limit for the this site.

      I updated a bit with helper ranges

      Performance is a bit better, but not significantly.

      =IFERROR(MID($B2,
   SEARCH( N$4,
      SUBSTITUTE( " " & $B2,N$2,N$4,N$3)
      ) + N$5-2,
      IFERROR(
         AGGREGATE(15,6,
            1/( SEARCH( $N$4:$V$4,
                  SUBSTITUTE( " " & $B2,$N$2:$V$2,$N$4:$V$4,$N$3:$V$3
                  )
               )-1 >
               SEARCH(  N$4,
                  SUBSTITUTE( " " & $B2,N$2,N$4,N$3)
               )  )*
               SEARCH(  $N$4:$V$4,
                  SUBSTITUTE( " " & $B2,$N$2:$V$2,$N$4:$V$4,$N$3:$V$3
                  )
               ),1
         ),
      LEN($B2)+1
      ) -
         SEARCH( N$4,
            SUBSTITUTE( " " & $B2,N$2,N$4,N$3)
       ) - N$5+1
),"")

       

       

      • steveccc's avatar
        steveccc
        Brass Contributor
        Dec 04, 2019

        SergeiBaklan 

        I think I figured it out.

        I changed 

        LEN($B2)+1

         

        To
        LEN($B2)+2

        And it works now. Do you think this is the best change that could be made?

         

Resources