Extract Data from Single Cell. Column is Constant. Number of Rows Involved Changes. Number of Rows Within a Single Cell Can be 1 Up to 9

[helpmeplz_]

Hi, I am on a work computer so I can't download the plug to create a cool screenshot of data, but basically what I am trying to solve is that I download a file from a database that has a variable number of rows, but the columns are constant. I want to extract all data from column Y into up to 9 columns to the right of column AE (so beginning in column AF). The data in each row in column Y contains several rows within each cell (1-9 rows). This data looks like this:

Row	Column Y
1	New Suppliers: 100%
2	New Suppliers: 10% New Operating Model: 10% SKU Consolidation: 10% Supplier Consolidation: 10% Demand Management: 10% Volume: 50%
3	Strategic Negotiation: 10% Sourcing Tool & Analytics: 50% Commodity Market: 40%

The 9 columns with potential data (that would be extracted starting in column AF) would be named:

New Suppliers
New Operating Model
SKU Consolidation
Supplier Consolidation
Demand Management
Volume
Strategic Negotiation
Sourcing Tool & Analytics
Commodity Market

For each column, I would want to extract the corresponding percentage assigned to each in the same row (if applicable).

 

[Cubist]

How about this?

Book1
	Y							AF	AG	AH	AI	AJ	AK	AL	AM	AN
1								New Suppliers	New Operating Model	SKU Consolidation	Supplier Consolidation	Demand Management	Volume	Strategic Negotiation	Sourcing Tool & Analytics	Commodity Market
2	New Suppliers: 100%							100%
3	New Suppliers: 10% New Operating Model: 10% SKU Consolidation: 10% Supplier Consolidation: 10% Demand Management: 10% Volume: 50%							10%	10%	10%	10%	10%	50%
4	Strategic Negotiation: 10% Sourcing Tool & Analytics: 50% Commodity Market: 40%													10%	50%	40%
Sheet7

Cell Formulas
Range	Formula
AF2:AN4	AF2	=LET( a,XMATCH(TEXTBEFORE(TEXTSPLIT(Y2,CHAR(10)),":"),$AF$1:$AO$1), b,TEXTAFTER(TEXTSPLIT(Y2,CHAR(10)),":"), XLOOKUP(SEQUENCE(1,9),a,b,""))
Dynamic array formulas.

 

[JEC]

Here also a Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = {"New Suppliers", "New Operating Model", "SKU Consolidation", "Supplier Consolidation", "Demand Management", "Volume", "Strategic Negotiation", "Sourcing Tool & Analytics", "Commodity Market"},
    nCol = Table.AddColumn(Source, "Custom", each
             let 
                tbl = Table.FromRows(List.Transform(Text.Split([Info],"#(lf)"), (x)=> Text.Split(x,": "))),
                colNames = List.Select(cols, (x)=> List.PositionOf(tbl[Column1],x)>=0),
                tblNew = Table.ReorderColumns(Table.PromoteHeaders(Table.Transpose(tbl)),colNames)
             in
                tblNew      
        ),
    result = Table.ExpandTableColumn(nCol, "Custom", cols, cols)
in
    result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Nr	Info
2	1	New Suppliers: 100%
3	2	New Suppliers: 10% New Operating Model: 10% SKU Consolidation: 10% Supplier Consolidation: 10% Demand Management: 10% Volume: 50%
4	3	Strategic Negotiation: 10% Sourcing Tool & Analytics: 50% Commodity Market: 40%
5
6
7
8
9
10	Nr	Info	New Suppliers	New Operating Model	SKU Consolidation	Supplier Consolidation	Demand Management	Volume	Strategic Negotiation	Sourcing Tool & Analytics	Commodity Market
11	1	New Suppliers: 100%	100%
12	2	New Suppliers: 10% New Operating Model: 10% SKU Consolidation: 10% Supplier Consolidation: 10% Demand Management: 10% Volume: 50%	10%	10%	10%	10%	10%	50%
13	3	Strategic Negotiation: 10% Sourcing Tool & Analytics: 50% Commodity Market: 40%							10%	50%	40%
Sheet1

 

[helpmeplz_]

.

 

[helpmeplz_]

How about this?

Book1
	Y							AF	AG	AH	AI	AJ	AK	AL	AM	AN
1								New Suppliers	New Operating Model	SKU Consolidation	Supplier Consolidation	Demand Management	Volume	Strategic Negotiation	Sourcing Tool & Analytics	Commodity Market
2	New Suppliers: 100%							100%
3	New Suppliers: 10% New Operating Model: 10% SKU Consolidation: 10% Supplier Consolidation: 10% Demand Management: 10% Volume: 50%							10%	10%	10%	10%	10%	50%
4	Strategic Negotiation: 10% Sourcing Tool & Analytics: 50% Commodity Market: 40%													10%	50%	40%
Sheet7

Cell Formulas
Range	Formula
AF2:AN4	AF2	=LET( a,XMATCH(TEXTBEFORE(TEXTSPLIT(Y2,CHAR(10)),":"),$AF$1:$AO$1), b,TEXTAFTER(TEXTSPLIT(Y2,CHAR(10)),":"), XLOOKUP(SEQUENCE(1,9),a,b,""))
Dynamic array formulas.

This works, but the data comes out as either text or something else. It can't be summed to ensure it's 100% total. Pivot table doesn't recognize it as a number either.

 

[Cubist]

This will return as decimal. You can format the cells as % if needed.

=LET(
a,XMATCH(TEXTBEFORE(TEXTSPLIT(Y2,CHAR(10)),":"),$AF$1:$AO$1),
b,TEXTAFTER(TEXTSPLIT(Y2,CHAR(10)),":"),
XLOOKUP(SEQUENCE(1,9),a,NUMBERVALUE(TRIM(b)),""))

 

[helpmeplz_]

This will return as decimal. You can format the cells as % if needed.

=LET(
a,XMATCH(TEXTBEFORE(TEXTSPLIT(Y2,CHAR(10)),":"),$AF$1:$AO$1),
b,TEXTAFTER(TEXTSPLIT(Y2,CHAR(10)),":"),
XLOOKUP(SEQUENCE(1,9),a,NUMBERVALUE(TRIM(b)),""))

That's it, worked perfectly! Thank you!