Excel公式需求：筛选含非零计数的字符串并提取对应数字

需求说明 #

判断某行是否包含指定字符串（如*hread = 60*），仅筛选出不含0:的条目，理想结果是提取冒号前的非零数字，或返回包含该数字的对应字符串。

示例数据：

一、Excel 365 动态数组方案（简洁高效） #

Excel 365支持动态数组和新函数，能快速实现需求：

1. 提取冒号前的非零数字 #

在AH列目标单元格输入以下公式，自动返回所有符合条件的数字（用逗号分隔）：

=TEXTJOIN(", ",TRUE,FILTER(TEXTBEFORE(H25:AE25," : ",1),ISNUMBER(TEXTBEFORE(H25:AE25," : ",1)*1)*(TEXTBEFORE(H25:AE25," : ",1)<>0)*(ISNUMBER(SEARCH("hread = 60",H25:AE25)))))

逻辑拆解：

• SEARCH("hread = 60",H25:AE25)：检查单元格是否包含目标字符串
• TEXTBEFORE(...)：提取冒号前的内容
• ISNUMBER(...) *1：验证提取内容是数字格式
• ... <>0：排除数字为0的情况
• FILTER：筛选同时满足所有条件的内容
• TEXTJOIN：把多个结果用逗号拼接成一个字符串

2. 返回完整的符合条件的原字符串 #

如果需要保留原单元格的完整内容，使用这个公式：

=TEXTJOIN(", ",TRUE,FILTER(H25:AE25,ISNUMBER(SEARCH("hread = 60",H25:AE25))*(LEFT(H25:AE25,2)<>"0:")))

逻辑拆解：

• LEFT(H25:AE25,2)<>"0:"：直接排除以0:开头的单元格
• 其他逻辑同上，筛选后拼接结果

二、旧版Excel兼容方案（无动态数组） #

如果用的是旧版Excel（无FILTER、TEXTBEFORE函数），可以用数组公式实现：

1. 提取冒号前的非零数字 #

在AH25单元格输入以下公式，按Ctrl+Shift+Enter确认，然后下拉填充直到出现空值：

=IFERROR(INDEX(LEFT(H25:AE25,FIND(" : ",H25:AE25)-1),SMALL(IF((ISNUMBER(SEARCH("hread = 60",H25:AE25)))*(LEFT(H25:AE25,FIND(" : ",H25:AE25)-1)<>0)*(ISNUMBER(LEFT(H25:AE25,FIND(" : ",H25:AE25)-1)*1)),COLUMN(H25:AE25)-COLUMN(H25)+1,""),ROWS(AH$25:AH25))),"")

2. 返回完整符合条件的原字符串 #

同样在AH25输入公式，按Ctrl+Shift+Enter确认后下拉：

=IFERROR(INDEX(H25:AE25,SMALL(IF((ISNUMBER(SEARCH("hread = 60",H25:AE25)))*(LEFT(H25:AE25,2)<>"0:"),COLUMN(H25:AE25)-COLUMN(H25)+1,""),ROWS(AH$25:AH25))),"")

内容的提问来源于stack exchange，提问作者Michele