按用户名和邮箱域名拆分电子邮箱地址

需求说明 #

需要将电子邮箱地址拆分为两部分：

• 用户名：例如test.leo@gmail.com中的test.leo
• 不含.com后缀的域名：例如上述邮箱中的gmail

原公式解析（含中文释义） #

你提供的公式及其中文解释如下：

=IFERROR(SPLIT(REGEXREPLACE(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)), "[a-zA-Z0-9_]",""), "@"), "")

各部分功能：

• INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0))：从Filter工作表的A至AY列区域，匹配当前行A39对应的行、当前列N1对应的列，提取目标邮箱地址
• REGEXREPLACE(..., "[a-zA-Z0-9_]","")：将邮箱地址里的所有字母、数字、下划线替换为空字符
• SPLIT(..., "@")：按@符号拆分经过替换后的字符串
• IFERROR(..., "")：如果整个过程出现错误，返回空字符串

原公式问题 #

该公式存在逻辑错误：REGEXREPLACE会把邮箱中构成用户名和域名的核心字符全部删除，最终无法得到有效的用户名和域名内容。

正确实现方案 #

1. 单独提取用户名 #

公式：

=IFERROR(LEFT(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)),FIND("@",INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)))-1),"")

逻辑：通过FIND定位@符号的位置，再用LEFT截取该位置之前的所有内容，即为用户名。

2. 单独提取不含.com的域名 #

公式：

=IFERROR(MID(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)),FIND("@",INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)))+1,FIND(".com",INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)))-FIND("@",INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)))-1),"")

逻辑：

• 先用FIND找到@的位置，以此作为域名提取的起始点
• 再找到.com的位置，计算出需要截取的长度
• 最后用MID提取@之后、.com之前的内容，就是不含后缀的域名

3. 一次性拆分到相邻单元格 #

如果希望用一个公式直接将两部分拆分到相邻单元格，可使用以下公式：

=IFERROR(SPLIT(REGEXREPLACE(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)), "\.com$", ""), "@"), "")

逻辑：

• 先用REGEXREPLACE去掉邮箱末尾的.com后缀（\.com$匹配结尾的.com）
• 再用SPLIT按@拆分处理后的字符串，即可得到用户名和不含.com的域名

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