☰

如何使用 Excel 函数在单元格中查找特定的文本语法格式？

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用 Excel 函数在单元格中查找特定的文本语法格式？

我正在寻找一个单元格公式来查找/识别/验证相邻单元格是否包含正确格式的字符。

如果有字符“|”，那么它前面或后面必须有任意数量的文本，然后在文本结束前或有空格之前必须有另一个“|”，否则单元格是错误的。

例子：

|Name| |Surname| |City| = ok
|Name| |Surname| = ok
|Name| |Surname| New York = ok
New York |Name| |Surname| = ok
|Name| |City |Surname| = wrong
N|ame |City| |Surname| = wrong
|Surname| |ZipCode| = ok

有任何想法吗？

答案1

表述该问题的另一种方式是：

  1. |||不允许使用连续的，也不允许使用仅包含两个或更多空格* 的。
  2. 如果要验证的文本中存在| |（中间有一个空格），则它前面必须紧接着任意数量的非|文本，并在|或另一个| |之前紧接着那，和它后面必须紧接着任意数量的非|文本，后面跟着一个|或另一个| |。
  3. 如果没有，| |那么就一定没有| 或者正好两个|。

从技术上讲，条件 1. 在问题中被明确排除，（“任意数量的文本”可以表示无或者只允许空格），但可以从例子中推断出这是 OP 的意图。

通过重新表述上述条件，仅使用公式的解决方案变得显而易见，如下面的工作表所示：

工作表截图

这是输入的公式B2:B11：

=IF(CHOOSE(MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),TRUE,FALSE,AND(LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0)),"ok","wrong")

解释：

该公式的美化版本如下：

=
IF(
  CHOOSE(
    MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),
    TRUE,
    FALSE,
    AND(
      LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,
      LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0
    )
  ),
  "ok",
  "wrong"
)

上述三个条件可以重构如下：

[a] 必须比s （第一个和最后一个）|所占的数量恰好多 2 个。| |

和

[b] 如果存在|，则必须至少有两个，并且前两个必须至少由一个非空格字符分隔。

[a] 的公式为：

LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2

|[b] 的文本内验证部分的公式为：

LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0

[b] 的另一部分（即，不能只有一个|）由函数处理CHOOSE()，该函数还处理没有的情况|（因为这种边缘情况会导致公式 [b] 中的错误以及公式 [a] 的结果不正确），

该函数的第一个参数CHOOSE()，

MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|","")))

将 s的可能计数映射|到索引1、2和 ，3如下所示：[0,1,2,3,4,…]→ ，因此对于 的计数，[1,2,3,3,3,…]函数返回，对于 的计数，函数返回 ，对于所有其他计数，函数的结果。TRUE0FALSE1AND()

*|使用该功能可以放宽不允许两个或多个内部空间的条件TRIM()。

答案2

假设每个都|xyz|在一个单独的单元格中，则此公式将对|xyz|中的值执行此操作A1：

=IFERROR(IF(LEFT(A1,1)="|",IF(FIND("|",A1,2)=LEN(A1),"ok","not ok"),"not ok"),"not ok")

答案3

哇，这个问题真难！
但经过 4 小时的摸索，我想我找到了正确的 RegEx 模式

我创建了一个小型 VBA 函数，为您提供一种新类型的公式=RegExTest()。
该公式检查给定的模式是否与要检查的单元格匹配，并返回 true 或 false。

首先，我尝试搜索所有有效单元格。我失败了，因为模式太长了。
然后我想到：为什么不搜索无效的字符组合呢？

(\w+\|\w+|(^|\s)\w+\||\|\w+($|\s)|\|\|)

在此处输入图片描述

背后的想法

该模式有 4 个条件。如果其中任何一个条件与无效的部分字符串匹配，则公式将返回真的否则返回错误的这意味着选中的单元格中没有错误

CONDITION#1    or    CONDITION#2   or   CONDITION#3    or    CONDITION#4 
\w+ \| \w+          (^|\s) \w+ \|       \| \w+ ($|\s)          \| \|
"abc|abc"         "^abc|" or " abc|"   "|abc " or "|abc$"       "||"

RegEx 详细信息

  1. 像 (xxx|yyy|zzz) 这样的模式是一个具有 3 个条件的组，并且一个条件必须为真
  2. 该组合\|表示|必须转义的字符，因为|单独是一个特殊字符
  3. \w+代表所有字母 az 或数字 0-9 或下划线。由于以下用户定义函数使用该ignorecase = true设置，因此无需指定大写字母 AZ
  4. ^代表单元格值开始和$单元格值结束
  5. \s代表单个空格。我忽略了它也代表制表符和换行符的事实

来源：正则表达式语言 - 快速参考在 MSDN 上

如何在 Excel 中使用 RegEx

这是大多数人不知道的。实际上，您可以在任何 Office 程序中使用 RegEx。使用+
打开 VBA 编辑器，添加新的ALTF11模块并粘贴代码

Function RegExTest(rngCell As Range, strPattern As String) As Boolean        
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBScript.RegExp")        
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    objRegEx.Pattern = strPattern        
    RegExTest = objRegEx.Test(rngCell.Value)        
End Function

现在，一种新的公式可用：=RegExTest( <cell_to_check> , <RegEx_pattern> ) 它将返回真的如果模式与选中单元格值的任何位置匹配

如果有人需要，这里有一个示例工作簿

相关内容