Excel列提取邮箱地址公式返回冗余文本优化方案咨询

问题原因 #

你原本使用的公式核心逻辑是将空格作为分隔符拆分字符串，仅能正确提取前后带有空格的邮箱。当邮箱前后的分隔符为逗号、斜杠等非空格字符时，就会把分隔符和相邻的冗余内容一并提取，和你遇到的错误场景完全匹配。

解决方案 #

方案1：适用于Excel 365/2021及以上版本 #

用新增的函数简化写法，可灵活适配多种分隔符：

=LET(
  at_loc,FIND("@",A1),
  start_loc,MAX(IFERROR(FIND({" ",",","/"},A1,at_loc-ROW(INDIRECT("1:"&at_loc))),0))+1,
  end_loc,MIN(IFERROR(FIND({" ",",","/"},A1,at_loc+1),LEN(A1)+1))-1,
  MID(A1,start_loc,end_loc-start_loc+1)
)

如果需要提取同一单元格内的第N个邮箱，把第一行的FIND("@",A1)替换为FIND(REPT("@",N),SUBSTITUTE(A1,"@",REPT("@",N),N))即可，N为目标邮箱的序号。如果你的场景里还有其他分隔符，直接往{" ",",","/"}的大括号里添加对应字符就行。

方案2：兼容所有Excel版本的通用公式 #

没有LET函数的旧版本可以用嵌套写法，输入完成后按Ctrl+Shift+Enter数组回车生效：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""))-MIN(FIND({"0","1","2","3","4","5","6","7","8","9","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(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""))-ROW($1:$99),1))&"@"),ROW($1:$99))+1,MIN(FIND({" ",",","/","@"},A1&" ,/@",FIND("@",A1)+1))-FIND("@",A1)+MIN(FIND({"0","1","2","3","4","5","6","7","8","9","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(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"/"," ")," ",""))-ROW($1:$99),1))&"@"),ROW($1:$99))))

效果验证 #

用你给出的两个报错场景测试，均可得到正确结果：

• 示例1原始内容**TEST** John Beasley,jbeasley@usa.com,7575551212，提取结果为jbeasley@usa.com
• 示例2原始内容USA-USA/J Beasley/jbeasley@usa.com/757-555-1212，提取结果为jbeasley@usa.com

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