Excel formula help

A[L]C · 3 Oct 2007 at 09:01

Hi all

Ive got about 500 entries and need to search each cell in a column for the string "MF" (could be uppercase or lowercase) within each cell.

A selection example would be:

eb0l29
eb3l29
eb4mf57
eb5l72
FE2L4
MG0MF31
FLIL30
FN0IN48
FW1L53
FW2I39

I would therefore need the formula to pick out eb4mf57 and MG0MF31 from the above list

div0 · 3 Oct 2007 at 09:48

Not using a formula here, but it might be useful to you.

Select the whole column

Click 'Edit' -> 'Find'

In the 'Find What' box type 'mf'

Click 'Options'

Change the 'Look In' drop down to use 'Values'

Use 'Find All' or 'Find Next' to see which cells contain those values.

Haircut · 3 Oct 2007 at 10:09

=NOT(ISERR(FIND("MF",UPPER(cell you want to check))))

This will return TRUE for any cells containing MF (case insensitive) and FALSE for any others.

A[L]C · 3 Oct 2007 at 10:12

Thanks. Used

=IF((SEARCH("mf",B2,1))=4, "Yes", "No")

Haircut, can you explain how yours works?

D3lirious · 3 Oct 2007 at 12:51

looks like your will return a "yes" whenever "mf" is found at precisely the 4th character. Haircut's will return a "true" if "mf" is found anywhere in the cell.

A[L]C · 3 Oct 2007 at 12:54

D3lirious said:
looks like your will return a "yes" whenever "mf" is found at precisely the 4th character. Haircut's will return a "true" if "mf" is found anywhere in the cell.

Mine finds mf anywhere too

not as nice though