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.
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!
steveccc , you are welcome, glad to help
Hello Sergei, I'm trying to follow your Power Query instructions above. I'm new to Power Query, so none of it is intuitive. Are you able to post more detailed instructions? I searched a few videos on YouTube and could not find anything directly relevant, so I thought I could ask you. If you have a YouTube channel, please let me know. Here is what I figured out on my own.
Step 1:
Source data is in B1:B151.
In Excel worksheet, create a table of separators in D1:D10.Step 2:
Highlight source data B1:B151.
Goto Data drop down menu.
Select "From Range / Table"
This opens up Power Query.
Step 3:
Sadly, this is where I am stuck.
How do I do this?
"and with query add column to which transform separators, like "PG " => "* PG "
If you have time, I'd love to get step by step instructions because I'm brand new to Power Query. Thanks for considering this!