Forum Discussion
extract player names following text string references
Variant with Power Query is based on idea here https://bondarenkoivan.wordpress.com/2015/04/16/multiple-replacements-of-words-in-power-query/
First, we create table with separators
and with query add column to which transform separators, like "PG " => "* PG "
With above function we transform source table into
and split column into rows by "* " delimiter
adding index and conditional column we give unique number to each group of names
Filter nulls, remove Index and pivot Lineup.1 on Lineup.2 with rows aggregation. Remove Custom column and load result back to Excel sheet.
That is in the second sheet of the attached file.
If source data is in csv file you may connect it directly instead of data table as here.
If with formula I'd suggest first to wrap headers with spaces, i.e. use " PG " instead of "PG", and the formula
=IFERROR(MID($B2,
IF(RIGHT(D$1,LEN(D$1)-1)=LEFT($B2,LEN(D$1)-1),
0,
SEARCH(D$1,$B2)
) +
LEN(D$1),
IFERROR(
AGGREGATE(15,6,
1/(IFERROR(SEARCH($D$1:$K$1,$B2),1)> IF(RIGHT(D$1,LEN(D$1)-1)=LEFT($B2,LEN(D$1)-1), 1, SEARCH(D$1,$B2)))*
IFERROR(SEARCH($D$1:$K$1,$B2),1),1
),
LEN($B2)
) -
IF(RIGHT(D$1,LEN(D$1)-1)=LEFT($B2,LEN(D$1)-1),
1,
SEARCH(D$1,$B2)
) -
LEN(D$1)
),"")in D2, drag it to the right and all down.
Please check third sheet in attached file.
SergeiBaklan Wow this is amazing. Thanks Sergei Baklan!
One small thing, I noticed some players are missing the last letter of their name.
For example, Cell K13 is missing a "k" -- "Russell Westbroo", should instead be "Russell Westbrook".Cell G7 is missing an "e" -- "Paul Georg" should instead be "Paul George". Cell K15, K20, H23, H24, and a few others...
I tried modifying the code but it didn't work for me.That was for the first and last elements in the text. I corrected initial formula (4th sheet attached) and simplified it a bit
=IFERROR(MID($B2, SEARCH(D$1," " & $B2)-1 + LEN(D$1), IFERROR( AGGREGATE(15,6, 1/(SEARCH($D$1:$K$1," " & $B2)> SEARCH(D$1," " & $B2))* SEARCH($D$1:$K$1," " & $B2),1 ), LEN($B2)+1 ) - SEARCH(D$1," " & $B2) - LEN(D$1)+1 ),"")in a 5th sheet attached. Please check if it works.
SergeiBaklan This is perfect. It is a work of art. Thank you so much!
I see, sorry for the bug. I'll try to correct bit later.