How to check containment in Excel

Question

How can i chek if one value (e.g the string in A2) contains other value (e.g the string in B3)?

tenfour · Accepted Answer · 2011-03-17 12:21:46Z

up vote 2 down vote accepted

=IF(ISERROR(FIND(B3,A2)),"it's not there","it's there")

FIND returns the position in A2 where B3 is found, or #VALUE if it's not found.

ISERROR tests for #VALUE

answered Mar 17 '11 at 12:21

tenfour
1363

	thanks! it almost what i wanted. i managed to do this like this: =IF(ISERR(FIND(A2,B2)=TRUE,1,0) – aharon Mar 17 '11 at 12:27
	it would be nice to get at least an upvote for that... – tenfour Mar 17 '11 at 13:00
	You got an upvote from me! ISERR is slightly better than ISERROR here as it excludes the case where B3 or A2 contain a #N/A themselves (small possibility, but better to exclude any cases we don't need to consider). @aharon: There is no need to do the =TRUE bit, simply ISERR(FIND(foo, bar)) will evaluate to TRUE or FALSE, so testing if TRUE=TRUE is not helpful. – AdamV Mar 21 '11 at 10:48

Ernest Friedman-Hill · Answer 2 · 2011-03-17 12:23:45Z

up vote 0 down vote

You can use the FIND() function; it returns the index of string1 in string2 if it exists, or error otherwise.

answered Mar 17 '11 at 12:23

Ernest Friedman-Hill
457313

migrated from stackoverflow.com Mar 17 '11 at 13:00