Look for text then count number next to it

[JBC70]

Hi everyone - I'm hoping I can get some assistance on a formula for the following:

Currently I have a cell that contains (BOS4)(CAB11)(FRE101) - next to this cell I have 3 cells each the heading BOS, CAB & FRE. Under that headings I need a formula that will grab the number next to each in the cell and put that number in the cell under its corresponding heading ie formula is "Look for 'BOS' and place the number next to under the BOS heading.

Hope this makes sense?

Thank you very much in advance.

 

[Scott Huish]

Perhaps:

Book1
	A	B	C	D
1		BOS	CAB	FRE
2	(BOS4)(CAB11)(FRE101)	4	11	101
3	(CAB12)(FRE9)	0	12	9
Sheet1

Cell Formulas
Range	Formula
B2:D3	B2	=--(IFERROR(TEXTBEFORE(TEXTAFTER($A2,B$1),")"),0))

 

[JBC70]

Legend! Thank you! This worked a treat!