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.
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!One question - do you copy/paste or insert source data (B1:B151) from csv file? If so it'll be easier to query it, i could update the query if you give a sample of such file.