Extract multiple numbers from cell based on a criteria list

[raptor5618]

Item 1	10215 1601 4510 4542 6546 7271
Item 2	10215 10556 1601 4510 4542 6546 7271

in the cells with multiple numbers I need to know based on the item I need to know if the item has a number that matches a criteria list. I would be fine with changing the font of those numbers that match the criteria or extracting them to another cell. Currently I extract text to columns and then vlookup each column. (for some items there are 10 or 20 numbers.) Followed by a textjoin over the range of lookups It works but is a bit clunky and removing the #N/A's I only succeeded by putting a space so the result from text join has to be edited so that all the multiple spaces are removed. I tried find and replace which really was not ideal either but found an item but would change the criteria of the whole cell so bolding just the matching criteria didn't work either. I am looking for a method of pulling every number that matches the list of criteria and putting them into another cell. without having to extract and vlookup and textjoin. Thanks.

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book2
	A	B	C	D	E	F
1			Matches			List
2	Item 1	10215 1601 4510 4542 6546 7271	10215 6546			10215
3	Item 2	10215 10556 1601 4510 4542 6546 7271	10215 6546 10556			6546
4						10556
5						999
6						8888
Sheet7

Cell Formulas
Range	Formula
C2:C3	C2	=TEXTJOIN(" ",1,IF(ISNUMBER(FIND(" "&$F$2:$F$6&" "," "&B2&" ")),$F$2:$F$6,""))

 

[JamesCanale]

How about:

MrExcelPlayground4.xlsx
	A	B	C	D	E	F
27	Item 1	10215 1601 4510 4542 6546 7271		4510	10215
28	Item 2	10215 10556 1601 4510 4542 6546 7271		4510	10215	10556
29
30
31		List
32		4510
33		10215
34		10556
Sheet11

Cell Formulas
Range	Formula
D27:F28	D27	=IFERROR(MID(B27,TRANSPOSE(SEARCH($B$32:$B$34,B27)),SEARCH(" ",B27,TRANSPOSE(SEARCH($B$32:$B$34,B27))+1)-TRANSPOSE(SEARCH($B$32:$B$34,B27))),"")
Dynamic array formulas.

 

[Eric W]

James, your version will match partial numbers too. Change B32 to 510 to see. Also, you can shorten it a bit by just doing the TRANSPOSE at the end:

=IFERROR(TRANSPOSE(MID(B27,SEARCH($B$32:$B$34,B27),SEARCH(" ",B27,SEARCH($B$32:$B$34,B27)+1)-SEARCH($B$32:$B$34,B27))),"")

 

[JamesCanale]

yes... Very clever adding the space on each on of Col B.