如何在Google Sheets公式中嵌套TRIM去除查询结果的空白行？

问题分析 #

当前公式能从混合字符中提取11位数字，但因拆分后生成大量空值，导致结果出现多余空白行。无需额外嵌套TRIM（除非提取的数字前后有空格），优化公式逻辑即可直接过滤空值。

优化后公式（推荐） #

=IFERROR(FILTER(REGEXEXTRACT(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," "),"\d{11}"),REGEXEXTRACT(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," "),"\d{11}")<>""),"")

逻辑说明：

1. REGEXREPLACE(N3&" ","\D+"," ")：将非数字字符替换为空格，确保数字被空格分隔
2. SPLIT(..., " ")：按空格拆分字符串为数组
3. REGEXEXTRACT(..., "\d{11}")：从拆分后的每个元素中提取11位数字，非11位的返回空值
4. FILTER(..., ...<>"")：过滤掉数组中的空值，只保留有效11位数字
5. IFERROR(..., "")：处理无匹配结果的情况，返回空单元格

若需嵌套TRIM（处理数字前后含空格的场景） #

如果提取的数字前后可能存在空格，可在提取环节加入TRIM：

=IFERROR(FILTER(TRIM(REGEXEXTRACT(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," "),"\d{11}")),TRIM(REGEXEXTRACT(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," "),"\d{11}"))<>""),"")

原公式修改方案 #

如果要保留原公式结构，只需在SPLIT结果外层嵌套TRIM，并确保QUERY过滤逻辑生效：

=IFERROR(INDEX(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN(TRIM(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," ")))=11,TRIM(SPLIT(REGEXREPLACE(N3&" ","\D+"," ")," ")),""))),"where Col1 is not null ",0))),"")

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