主页 / computer / 问题 / 1804709
Accepted
Raptor
Asked: 2023-08-18 12:20:04 +0800 CST

Excel中如何过滤包含英文字符的行？

  • 772

我知道 Microsoft Excel 无法使用正则表达式。此外，过滤器功能只能接受2个自定义过滤器，例如包含“a”或包含“b”。

在单元格中包含英文和非英文字符的 Excel 文件中，如果 A 行包含任何英文字符（az，不区分大小写），如何过滤行？

ps 我不想编写宏脚本（VB 或 .NET 代码）

microsoft-excel

1 个回答

  1. Best Answer

    尝试下面的公式。它假设范围是单列。

    =LET(data,
       BYROW(B2:B7,
         LAMBDA(row,
           IF(SUM(IFERROR(
              FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},
                LOWER(row)),0)),row,""))),FILTER(data,data<>""))

    说明：该公式首先将字符转换为小写。然后，尝试查找该行中是否找到从 a 到 z 的字符。如果是，则对位置（在文本中找到字符的位置）进行求和。我们将得到一个大于 0 的数字。这适用于 B2 到 B7 范围内的每一行。最后，数据被过滤以删除不包含它们的行。

    更改 B2:B7 以匹配您的范围。

    编辑（基于OP的评论）：如果您必须根据列（B2到B5）过滤表（B2到F5），请调整以下公式：

    =FILTER(B2:F5,B2:B5=
       BYROW(B2:B5,
         LAMBDA(row,
           IF(SUM(IFERROR(
              FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},
                LOWER(row)),0)),row,""))))

    • 3

相关问题