Formula to display cell if the text content partially matches a search term

**Avestron** · 07-11-2013, 03:35 AM

Good day,

This thread relates to an earlier thread that I posted but is a different direction specifically pertaining to formulae.

If I were to have a value in a cell in sheet 'Source' equal to "Sesame Street" (hey, its just an example :P )

And another sheet 'Result' that displays or hides information based on whether the cell contents are equal to a 'search' cell contents:

=IF(Source!C9=Result!$C$1,Source!C9,"")

My intention would be to use this to display only when there is a match but here comes the tricky part:

How would I modify the above to check if the , for example, *Sesa* where the contents of Result!$C$1 in the formula above is 'sesa' ?

Any help appreciated

**martindwilson** · 07-11-2013, 03:51 AM

=IF(ISNUMBER(SEARCH(Result!$C$1,source!C9)),source!C9,"")

**Avestron** · 07-11-2013, 04:07 AM

Thank you very much martindwilson

Thanks also benishiryo (sorry if I wasn't clear enough in the original post).

With the minor modification: =IF(ISNUMBER(SEARCH(Result!$I$3,Source!D2)),Source!D2,"")

and applying =IF(AND($D2<>"",Source!C2<>""),Source!C2,"") to all other cells on the same row,

I now am able to filter out all sorts of unwanted rows from the Source sheet! It will greatly help me to perform my task for manual relinking (at least in terms of error avoidance).

**benishiryo** · 07-11-2013, 03:56 AM

hi Avestron. a little confused which cells has what values. say Source!C9 contains "Sesame Street" & Result!$C$1 contains "Sesa", then:
=IF(COUNTIF(Source!C9,"*"&Result!$C$1&"*"),Source!C9,"")

if it's the other way round, then:
=IF(COUNTIF(Result!$C$1,"*"&Source!C9&"*"),Source!C9,"")

@martin:
you added an extra quote here:
=IF(ISNUMBER(SEARCH(Result!$C$1",source!C9)),source!C9,"")
should be:
=IF(ISNUMBER(SEARCH(Result!$C$1,source!C9)),source!C9,"")