extracting numbers from other cells (search)

[Mus]

Hi All

Is there a way in excel to create a search cell?? ie if i have a column with various numbers ie. each cell containing numbers with comma like 12,2,7,34,5,21 etc (say 10 cells in a coulumn with various numbers separated by commas) is there a way of extracting number from the cells which i enter as a search, if i wanted to search 1,2,3,4,5,6 from the cells is there a way excel can show me which cells contain these numbers?????

 

[Dave Peterson]

my newsreader crashed just after I hit the send button--sorry if this
duplicates.

I'd use a series of help columns.

Put your data in A2:A999 (headers in Row 1)

Put the first number to search for in B1.
Put this formula in B2:
=ISNUMBER(SEARCH(","&B$1&",", ","&SUBSTITUTE(A2," ","")&"," ))

It adds leading and trailing commas to your numbers and to the number in B1.

So 12,2,7,34,5,21 will be treated like: ,12,2,7,34,5,21,
And if you put 2 in b1, it'll be treated like: ,2,

So you won't have to worry about finding 2 in 12 or 21.

Then apply data|filter|autofilter to your range.

Filter on True's (matches).