Forum Discussion
Solved
extract player names following text string references
Hello, I have converted a csv file into an Excel workbook. I would like to extract player names between specific text strings. The data is in column B. I would like to extract the player names in ...
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.
Hello,
It will be good to use Text To Column to split all the data by the Space delimiter (see the same excel file with the solution attached)
1. Select all from B2 downward using CTRL + SHIFT + Down Arrow.
2. Click on Text To Column in the Data tab
3. Click on Next.
4. In the Step 2 of 3 wizard dialogue box, uncheck Tab and check Space
5. Click Next
6. Optionally, you cab choose Cell Destination.
7. Click Finish
Then, use CONCATENATE or CONCAT function to join First and Last Name.
That's what I did in the solution attached. Kindly let me know if this helps...
Regards,
It will be good to use Text To Column to split all the data by the Space delimiter (see the same excel file with the solution attached)
1. Select all from B2 downward using CTRL + SHIFT + Down Arrow.
2. Click on Text To Column in the Data tab
3. Click on Next.
4. In the Step 2 of 3 wizard dialogue box, uncheck Tab and check Space
5. Click Next
6. Optionally, you cab choose Cell Destination.
7. Click Finish
Then, use CONCATENATE or CONCAT function to join First and Last Name.
That's what I did in the solution attached. Kindly let me know if this helps...
Regards,
- Thanks very much for your time Abiola1. Your solution would work, but I'd like to use a less labor intensive process that uses functions and formulas if possible. I feel like I'm half way there using something close to this: =TRIM(MID($G2,FIND(H$1,$G2)+3,10))
