Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

Suppose I have two columns of data. One is a list of names and the other is a list of peoples' names mixed with other stuff.

For instance, one list has "Bob" while the other might have "blank blank Bob blank blank" amongst other cells. Next row would be "Alice" and in the other "Alice blank blank". (assume they aren't sorted in any way"

What would be a formula to check if a cell in the first column is present (a substring) of any cell in the second column?

share|improve this question
Check out the FIND and SEARCH functions! – Peter Albert Mar 15 at 19:37

migrated from stackoverflow.com Mar 16 at 6:31

1 Answer

up vote 2 down vote accepted

You can use COUNTIF with a wildcard, e.g. if "Bob" is in A1 then you can check whether that exists somewhere in B1:B10 with this formula

=COUNTIF(B1:B10,"*"&A1&"*")>0

that formula will return TRUE if A1 exists anywhere in B1:B10 - it's not case-sensitive

share|improve this answer
That's how you do that! I had no idea the ""&A1&"" existed. Thank you! – Astephen2 Mar 16 at 1:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.