Forum Discussion

steveccc's avatar
steveccc
Brass Contributor
Nov 30, 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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 02, 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 

 

Sergei, I took your latest player data extract example and tried to apply it to a third format. 

 

In column I, the player's name that is extracted has the last letter cut off (see attached). I tried modifying the code but couldn't figure it out.

 

It seems like the issue is related to Cell S5, and the RIGHT function in the Column I formula, but I couldn't get it to work.

 

How do I modify this so the last letter of the player name in Column I is not cut off? Thanks!