Formula to sum numbers from cells containing certain text, but text and numbers are in same cell

[Angelique_C]

Hi, would someone be able to help please?

Within a table (and the table needs to grow), I have numbers and text (separated by a space). I need to sum the numbers that have common text within the table. I have uploaded a file - with the formula that I'm trying embedded in cells in row 3. This is the formula, which I found on the web but is not working as it should;

=SUM(IF(ISNUMBER(FIND(B1,Table1[[Participant 1]:[Participant 14]])),VALUE(LEFT(Table1[[Participant 1]:[Participant 14]],FIND(B1,Table1[[Participant 1]:[Participant 14]])-1)),0))

Can anyone offer a suggestion?

 

[Deepak]

Hi,

Check this.

If not the correct output then share the expected result also.

 

[Angelique_C]

Thanks, that's working great! Can I ask for some more help, building on the example file; Can you advise on how to create a new table that places all entries that have the same text, in the same column but on a new row (and copying the name of the event in column A into the new row that each cell entry belongs to. See Sheet 2 of uploaded file