Searching for data string in list of numbers

**Sweetypie** · 04-25-2010, 07:46 PM

Hope the title wasn't unclear.

I have a list of numbers in column A (3 and 4 -digit numbers).
In column C (say in C2)I have a a string to test against column A.
I would all the values in A that meet the string test to be reported in column E.

Example
column A
3408
116
9142
063
5127

Test string in Column C is 125679

It would report in Column E
116
5127

because only these two values meet the test--ie they are comprised of digits from the test string and only from the test string. In other words a number in column A is reported if and only of its digits are made up from the test string in column C

**shg** · 04-25-2010, 07:58 PM

With 125679 in C1, in B1 enter and copy down,

=IF(AND(ISNUMBER(FIND(MID(A1, {1,2,3,4}, 1), $C$1 & ""))), A1, "")

**NBVC** · 04-25-2010, 09:38 PM

Further, if you want to list those matches in a new column without blanks..

Then start in row 2, and change shg's formula in B2 to:

=IF(AND(ISNUMBER(FIND(MID(A2, {1,2,3,4}, 1), $C$2 & ""))), COUNT($B$1:B1)+1, "")

and copy down.. where C2 contains your search string.

then in C3 enter:

=MAX(C:C)

and in E2:

=IF(ROWS($A$1:$A1)>$C$3,"",INDEX(A:A,MATCH(ROWS($A$1:$A1),B:B,0)))

copied down as far as you want.

Searching for data string in list of numbers

LinkBack

Thread Tools

Rate This Thread

Display

Searching for data string in list of numbers

Re: Searching for data string in list of numbers

Re: Searching for data string in list of numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions