Excel formula help

[A[L]C]

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]

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]

=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]

Thanks. Used

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

Haircut, can you explain how yours works?

 

[D3lirious]

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]

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