Sum formula & Lookup on another column

[Mooncake1]

Hi all.

I'm attempting match col A with embedded name on column E.
so far i have extracted the match, but I want to add up all the values pertaining to the hardcoded list in A:A.
also the number after the name is always one space apart from the end of the name. like "dsgdf 100"

See below

Book1.xlsx
	A	B	C	D	E	F
2	Hardcode list	Sum			Changing List
3	58fdghj	32			105ghjfk 54	105ghjfk
4	1dfsh	23			133fdghj 11	133fdghj
5	133fdghj	22			142dgfh 457	142dgfh
6	74dgfh	22			156jg 65754	156jg
7	142dgfh	457			16tyh 78	16tyh
8	156jg	131508			170fgh 546	170fgh
9	98hj	12			1dfsh 23	1dfsh
10	105ghjfk	108			58fdghj 32	58fdghj
11	170fgh	546			74dgfh 22	74dgfh
12	16tyh	78			98hj 4	98hj
13					105ghjfk 54	105ghjfk
14					156jg 65754	156jg
15					98hj 4	98hj
16					156jg 65754	156jg
17					133fdghj 11	133fdghj
18					98hj 4	98hj
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=SUBSTITUTE(LEFT(E3,SEARCH(" ",E3,3))," ","")

 

[Mooncake1]

Just to add to this,
I would like to do this all in the one formula instead of splitting it like in my example

 

[Alex Blakenburg]

See if this works for you:
(it will only work in 365 though)

20210919 Filter using Contains Wildcard.xlsx
	A	B	C	D	E
1	Hardcode list	Sum			Changing List
2	58fdghj	160			105ghjfk 54
3	1dfsh	23			133fdghj 11
4	133fdghj	22			142dgfh 457
5	74dgfh	22			156jg 65754
6	142dgfh	457			16tyh 78
7	156jg	197262			170fgh 546
8	98hj	12			1dfsh 23
9	105ghjfk	108			58fdghj 32
10	170fgh	546			74dgfh 22
11	16tyh	78			98hj 4
12	XXXX	0			105ghjfk 54
13					156jg 65754
14					98hj 4
15					58fdghj 128
16					156jg 65754
17					133fdghj 11
18					98hj 4
Data

Cell Formulas
Range	Formula
B2:B12	B2	=LET(found,TRIM(FILTER($E$2:$E$18,ISNUMBER(SEARCH(A2,$E$2:$E$18)),0)), IFERROR(SUM(VALUE(RIGHT(found,LEN(found)-SEARCH(" ",found)))),0))

 

[jasonb75]

Without 365

Book1
	A	B	C	D	E
2	Hardcode list	Sum			Changing List
3	58fdghj	32			105ghjfk 54
4	1dfsh	23			133fdghj 11
5	133fdghj	22			142dgfh 457
6	74dgfh	22			156jg 65754
7	142dgfh	457			16tyh 78
8	156jg	197262			170fgh 546
9	98hj	12			1dfsh 23
10	105ghjfk	108			58fdghj 32
11	170fgh	546			74dgfh 22
12	16tyh	78			98hj 4
13					105ghjfk 54
14					156jg 65754
15					98hj 4
16					156jg 65754
17					133fdghj 11
18					98hj 4
Sheet1

Cell Formulas
Range	Formula
B3:B12	B3	=SUM(IFERROR(VALUE(SUBSTITUTE($E$3:$E$18,A3,"")),))

 

[Mooncake1]

Thanks for the help everyone