单元格字符串包含检测及特定URL与数据库字符串匹配查询技术问询

Got it, let's break down solutions for your two Google Sheets tasks—both are totally manageable with built-in functions, no complex scripts required.

This is one of the most common checks in Sheets, and you’ve got a couple solid options depending on your needs:

• Case-insensitive check (most common): Use REGEXMATCH for a clean TRUE/FALSE output. If you want to check if cell A1 contains the string "example", use:

  =REGEXMATCH(A1, "example")
  

  This will return TRUE if the string exists anywhere in A1, FALSE if not.

• Case-sensitive check: If you need to strictly match uppercase/lowercase, swap in FIND (which is case-sensitive) wrapped in NOT(ISERROR()):

  =NOT(ISERROR(FIND("Example", A1)))
  

  This only returns TRUE if "Example" (with the capital E) appears exactly as written in A1.

Assuming your "database" of strings is stored in a column (say, column B, with no empty cells cluttering it up), here’s a formula that checks if a URL in cell A1 contains any of those strings:

=SUMPRODUCT(--REGEXMATCH(A1, JOIN("|", B:B))) > 0

Let me break this down so you know how it works:

1. JOIN("|", B:B) takes all the strings in column B and combines them into a single regex pattern separated by | (which means "OR" in regex).
2. REGEXMATCH(A1, [that pattern]) checks if the URL in A1 matches any of the combined strings, returning TRUE for matches and FALSE otherwise.
3. The -- converts those TRUE/FALSE values to 1s and 0s.
4. SUMPRODUCT adds up all those 1s and 0s—if the total is greater than 0, that means at least one string from your database was found in the URL.

If column B has empty cells, add a FILTER to exclude them and avoid regex errors:

=SUMPRODUCT(--REGEXMATCH(A1, JOIN("|", FILTER(B:B, B:B<>"")))) > 0

内容的提问来源于stack exchange，提问作者Jakub Łanda