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

**benoj2005** · 02-15-2012, 03:27 AM

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** · 02-15-2012, 03:51 AM

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** · 02-15-2012, 04:00 AM

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** · 02-15-2012, 04:08 AM

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