Excel 2007 : If contains "text" then "Write this" or "This"

[benoj2005]

If cell contains certain text.xlsx

I want any cell that contains:

mal to say MAL
len to say LEN
sol to say SOL
atlas to say MAL

I have done an if statement and it only works for the first true outcome, it does not seem to carry on the arguement if false like a normal if statement?

[sweep]

Hi,

Try this:

=IF(ISNUMBER(SEARCH("mal",A2)),"MAL",IF(ISNUMBER(SEARCH("atlas",A2)),"MAL",IF(ISNUMBER(SEARCH("len",A2)),"LEN",IF(ISNUMBER(SEARCH("sol",A2)),"SOL",""))))

[benoj2005]

Worked a treat thanks very much!

Just so I understand, what is the isnumber formula? I feel silly because it makes me think if its a number then search, but this is text and numbers?

[sweep]

On its own, the function =SEARCH(find_text,within_text) will return the position of find_text in the string that is within_text, and if not present, return an error.

so,

=SEARCH("rent","Current") will return 4, as "rent" is at the 4th character of "Current"

=SEARCH("rent","Sultana") will return #VALUE as "rent is not present in "Sultana"


=ISNUMBER(value) returns either true or false depending on whether or not value is a number, so,

=ISNUMBER(SEARCH("rent","Current")) will return TRUE
=ISNUMBER(SEARCH("rent","Sultana")) will return FALSE