Searching within a single cell and summing up relevant values

Hi,

i have a single cell that has data in the following format

"name age *** height weight date name(1) age(1) ***(1) height(1) weight(1) date(1) name(2) age(2) ***(2) height(2) weight(2) date(2) name(3) age(3) ***(3) height(3) weight(3) date(3)"

example: "tom 41 male 190 80 5/12/2015 bill 29 male 174 70 5/14/2015 sarah 19 female 140 45 5/14/2015 cheryl 29 female 170 80 5/14/2015"

1. the terms are all separated by a single space
2. i cant use text to column

How may i use excel formula or vba to sum the weight of all the applicants with a particular date?

i.e. i want to sum the weight of bill, sarah and cheryl to obtain 195 as their 3 data series are dated 5/14/2015


Thank you for the help in advance!

Hi Glenn,

thank you for the help. But i'm afraid it's not scalable with all the array formulas.

I have attached a sample workbook, have to change the way i framed the problem because i realised the data has other problems (sorry, have to desensitise data)

I am looking to


=> sum all the "variable length" values which comes before a "desired" date. and unfortunately the data is all within a single cell (can't delimit it due to other reasons).
=> in this case (in the workbook), i am referring to the change in wealth

Thank you in advance