Function for comparing text and number combinations

[Guest]

I'm trying to compare a cell value with a column of cells that contain a mix
of numbers and text but I want to ignor the text. The values in the cells are
2 numbers followed by a letter then 3 or 4 numbers, eg. 45F254 or 56G2541. I
would like to get a 'true' statement for cells that contain 45D254, 45T254 or
56E2541, 56Y2541 and a 'false' return for 45F587 or 56R5874 etc. Is there a
function that accomplish this?

 

[Balazs - ExcelForums.com]

=ISNUMBER(FIND(A1,A3)

A1 should be your "key" as in 25
an
A3 is the cell you are searchin

If A3 Contains 45D254 and A1 contains 254, it will return TRU
If A3 Contains 45F587 and A1 contains 254, it will return FALS

Good luc
Balaz

PS FIND is case sensitiv

 

[SidBord]

What is the criteria for rejection? Is it letters "F" and
"R"? It seems like you might be able to write a "FOR" loop
using the "SEARCH" function to examine each "number" to see
if the rejection letters are present. That means you'd
have to write a Visual Basic macro, but it should be easy
to do.

 

[Balazs - ExcelForums.com]

The criteria in his example was the number 254, not the letter

 

[IC]

You could initially compare the relative lengths of the cells.

With the key in A1 and the cells to compare in column B:
In C1
=IF(LEN(B1)-LEN($A$1)<>3,FALSE,ISNUMBER(FIND($A$1,B1)))
Copy this down colum C as far as necessary.


HTH

Ian