Extract numeric value from cell equivalent to COlumn header

[surkdidat]

Hi
I have data in a cell (see initial data) where I want to be able to split out the numeric values out as per the column header.

I would be looking at putting the animal (in this example) and would want the "quantity" before the "x"

Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260	1
45x Dogs			45

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2	2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
3	2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260
4	45x Dogs			45
Main

Cell Formulas
Range	Formula
B2:G4	B2	=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(LOWER($A2),",","x "),"x ","</m><m>")&"</m></k>","//m[.='"&LOWER(B$1)&"']/preceding::m[1]"),"")

But the header values must be an exact match as can be seen in G3

 

[JvdV]

Book1
	A	B	C	D	E	F	G
1	Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2	2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
3	2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260
4	45x Dogs			45
Sheet4

Cell Formulas
Range	Formula
B2:G4	B2	=IFERROR(--SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(UPPER($A2),", ","</s><s>")&"</s></t>","//s[translate(.,'0123456789','')='X "&UPPER(B$1)&"']"),"X "&UPPER(B$1),),"")

 

[surkdidat]

Thank you - is there anyway it could have the header as the singular and have a wildcard after so it picks up on both the singular
and plural versions at all please?

Book1
	A	B	C	D	E	F	G
1	Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2	2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
3	2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260
4	45x Dogs			45
Sheet4

Cell Formulas
Range	Formula
B2:G4	B2	=IFERROR(--SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(UPPER($A2),", ","</s><s>")&"</s></t>","//s[translate(.,'0123456789','')='X "&UPPER(B$1)&"']"),"X "&UPPER(B$1),),"")

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2	2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
3	2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260
4	45x Dogs			45
Main

Cell Formulas
Range	Formula
B2:G4	B2	=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(LOWER($A2),",","x "),"x ","</m><m>")&"</m></k>","//m[.='"&LOWER(B$1)&"']/preceding::m[1]"),"")

But the header values must be an exact match as can be seen in G3

 

[Fluff]

You could use

=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(LOWER($A2),",","x "),"x ","</m><m>")&"</m></k>","//m[contains(.,'"&LOWER(B$1)&"')]/preceding::m[1]"),"")

 

[surkdidat]

Thank you once again Fluff, and also to JvdV - your help is very much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.