检查单元格是否包含来自另一个单元格的关键字-Google表格 [英] Check if cell contains keywords from another cell - Google Sheets

问题描述

我正在Google表格中为我的业务整理一个社交媒体内容模板.它主要具有简单的公式和条件格式来检查长度，而不是长度.

I'm putting together a social media content template for my business in Google Sheets. It will mostly have simple formulas and conditional formatting to check for length and what not.

现在，我正在努力解决以下问题:

now, what I'm struggling with is the following:

我有一个单元格，我们称它为A2-用户将在此处输入其内容.在C2中，我有一个关键字列表，以逗号作为定界符，它们是供用户用于SEO的关键字".

I have a cell, let's call it A2 - A user will enter their content in here. in C2 I have a list of keywords, with a comma as a delimiter which are "keywords" for the user to use for SEO purposes.

我希望有人可以告诉我一些巫术来完成以下任务.

I was hoping someone could tell me some wizardry to do the following.

要检查单元格A2中是否有 any ，请在c2中输入单词并输出使用count函数的单词数.

To check Cell A2 for any, words in c2 and output how many words are used with a count function.

因此，对于以下内容

Text                             Keywords       Count
Stackoverflow is a lovely place. Place, lovely. 2

我曾考虑过使用嵌套在IF语句中的RegexMatches(可能带有COUNT).

I have thought about using RegexMatches nested within an IF statement, possibly with a COUNT.

到目前为止，我已经提出了这个建议，但是我认为我并没有以正确的方式解决这个问题.

I've come up with this so far, but I don't think I'm going about this the right way.

=if(REGEXMATCH(A2, C2), "", "fail")

我也喜欢Find，但无法弄清楚.

I've also toyed with Find, but couldn't figure it out.

=if(A2=(find(C2)),"Found", "fail")

由于我正在与一个教育性非政府组织的博士/学科专家打交道，要想让他们遵守SEO很难，而无需大量的人工干预，因此这将是很大的帮助.

As I'm dealing with PhD/Subject specialists in an education NGO trying to get them to adhere to SEO is quite difficult without a lot of manual intervention, so this would be a big help.

演示页. https://docs.google.com/spreadsheets/d/15_7ZeJwCtQ91 edit?usp = sharing

更令人惊奇的是返回自定义字符串来代替count函数，但这可能会扩展它.

what would be more amazing would be to return custom strings inplace of the count function but that's probably stretching it.

希望这很清楚，如果没有，请告诉我，我将其改写.

Hopefully this is clear, if not please let me know i'll rephrase it.

干杯， DN.

推荐答案

假定关键字由|而不是,隔开，

Assuming keywords are separated by | instead of ,,

=LEN(REGEXREPLACE(REGEXREPLACE(A2,"(?i)\b"&C2&"\b","㊙"),"[^㊙]",))

• 内部替换将所有关键字更改为㊙
• 外部正则表达式将㊙以外的其他所有内容更改为
• 返回剩余字符串的长度
• Inner REPLACE to change all keywords to ㊙
• Outer REGEX to change everything else other than ㊙ to
• Return the LENgth of remaining string

这篇关于检查单元格是否包含来自另一个单元格的关键字-Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！