清洗销售数据的3个Excel公式:提取邮箱域名、规范电话并整理公司名称后缀

Habib Benabdeslam
Habib Benabdeslam
2 分钟读完
用Excel公式 清洗销售数据
清洗销售数据的3个Excel公式:提取邮箱域名、规范电话并整理公司名称后缀
目录
  1. 从邮箱地址提取域名
    1. 在旧版 Excel 中提取邮箱域名
    2. 向下填充前检查邮箱异常值
    3. 快速清洗并规范电话号码
    4. 清理公司名称法律后缀并规范大小写
  2. FAQ

当你需要在导入 CRM 前提取邮箱域名、统一电话号码文本并检查公司名称时,可以使用这些清洗销售数据的 Excel 公式。请保留原始列,并将每个处理结果写入新列,方便核对具体改动。

我们先来看几类棘手的数据:以 0 开头的电话号码、缺少邮箱分隔符的地址,以及包含类似法律实体后缀字样的公司名称。只在第一行整洁数据上有效的公式,向下填充后可能会破坏这些特殊值。

本指南将介绍三项操作:

📌 时间有限?先看摘要

通过三个针对性的 Excel 转换,为经过检查的 CRM 导入整理销售数据。将源数据与处理结果并排保留,并在替换任何内容之前检查异常值。

  • 输入: 邮箱地址、电话号码文本和公司名称。
  • 输出: 提取的域名、删除指定标点后的电话号码文本,以及按照明确后缀规则清理的公司名称。

从邮箱地址提取域名

在 Excel 中,从邮箱地址列表提取域名有两种方法,下面分别说明。

第一种:使用 TEXTAFTER 函数。该函数适用于 Microsoft 365、Excel 网页版和 Excel 2024。请查看 Microsoft 的最新函数文档,确认你的版本是否支持。

第二种:组合使用 RIGHT、LEN 和 FIND 函数——适合无法使用 TEXTAFTER 的用户。

按照以下步骤从邮箱地址中提取域名:

  1. 在公式栏中输入 =TEXTAFTER
  2. 选择包含第一个邮箱地址的单元格,例如 B3
  3. 将 @ 添加为查找文本

完整的 Excel 公式如下:

=TEXTAFTER(B3,"@")
从邮箱地址中提取域名
从邮箱地址中提取域名

要将公式应用到所有邮箱地址,请拖动公式单元格右下角的填充柄。

从邮箱列表中提取域名
从邮箱列表中提取域名

在旧版 Excel 中提取邮箱域名

接下来介绍如何在不支持 TEXTAFTER 函数的旧版 Excel 中提取邮箱域名。

我们要使用的公式是 =RIGHT(B3,LEN(B3)-FIND("@",B3))。

使用时,将公式粘贴到公式栏,并应用于包含第一个邮箱地址的单元格。

使用 =RIGHT(B3,LEN(B3)-FIND("@",B3)) 公式提取第一个域名
使用 =RIGHT(B3,LEN(B3)-FIND("@",B3)) 公式提取第一个域名

这个公式的工作原理如下:RIGHT 函数从邮箱地址末尾提取字符,FIND 和 LEN 则共同计算需要提取的字符数。

以 john.doe123@abc.com 为例,它总共有 19 个字符:

LEN(B3) 返回 19。

FIND 在 john.doe123@abc.com 中查找 @ 符号,并告诉我们它位于第 12 个字符:

FIND("@",B3) 返回 12。

用 19 减去 12,得到的 7 会传递给 RIGHT 函数。 Excel 随后提取最后 7 个字符,最终得到 abc.com。

要将该公式应用到所有单元格,请向下拖动原公式单元格右下角的填充柄。

使用 =RIGHT(B3,LEN(B3)-FIND("@",B3)) 公式提取邮箱列表中的所有域名
使用 =RIGHT(B3,LEN(B3)-FIND("@",B3)) 公式提取邮箱列表中的所有域名

如果你更习惯使用 CSV,可以在 Datablist 中提取域名,并将结果与源数据列并排检查。

向下填充前检查邮箱异常值

如果缺少分隔符,TEXTAFTER(B3,"@") 会返回错误。可用于初步检查的公式是 =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"检查邮箱")。它只能提取文本,无法验证邮箱地址是否真实存在或能否正常收信。对于包含多个邮箱地址或多个 @ 字符的行,应将其标记为异常,而不要把提取出的末尾文本直接视为可信的公司域名。

例如,alex@example.com 会返回 example.com,而 alex.example.com 则需要人工检查。请将完整邮箱地址与域名并排保留,以免把同一家公司的两位联系人错误合并成一条联系人记录。

快速清洗并规范电话号码

下面分两步清洗并规范电话号码。

第一步,使用以下公式清理电话号码:

=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))

该公式只会删除列出的标点,并以文本形式返回结果。**不要添加 +0,也不要以其他方式把结果强制转换为数字:**否则会删除开头的 0 和国际号码中的 + 号。导入原始电话号码列时也应将其设为文本;如果 Excel 已经删除了开头的 0,公式无法将其恢复。Microsoft 在保留前导零的指南中解释了这一行为。

使用 TEXTSPLIT 需要受支持的 Excel 版本。本例使用英文函数名和逗号作为参数分隔符;本地化安装的 Excel 可能采用不同的函数名或分隔符。该公式不会验证国家或地区代码、分机号,也不能判断号码是否可以接通。

应用公式后的效果如下。

清理后的电话号码
清理后的电话号码

如下图所示,向下拖动原公式单元格右下角的填充柄,即可将公式应用到所有电话号码。

将清理公式应用到所有电话号码
将清理公式应用到所有电话号码

例如,文本 020 7946 0958 会变成 02079460958;+44 (20) 7946-0958 会变成 +442079460958。分机号应单独保存,并将包含字母或意外字符的号码标记出来以供检查。电话号码是一种标识符,不是用于计算的数值。

以下截图展示的是一种可选的美国号码显示格式。该方法只适用于已经检查过的美国 10 位电话号码,并且应在单独的显示列中使用。国际数据导出和 CRM 导入仍应保留清理后的文本;这一步只是调整显示格式,并不等同于国际电话号码标准化。

新建一个名为 Formatted Phone Numbers 的列。

用于填入格式化电话号码的新列
用于填入格式化电话号码的新列

选择标题下方的单元格,然后使用 Excel 快捷键 Command + 1 打开全部格式选项。

选择“Special”格式。

Excel 中的格式选项
Excel 中的格式选项

选择“Phone Numbers”以及电话号码所属的国家或地区。

选择美国电话号码格式选项
选择美国电话号码格式选项

💡 Excel 数字格式只能改变数值单元格的显示方式,无法补充缺失的国家或地区代码,也不能验证电话号码。对于国际号码列表,请保留文本值并检查国家或地区信息,再选择专门的电话号码清洗流程。

现在关闭设置面板,并确认 Excel 是否已应用该格式。

只需查看格式栏中是否显示“Special”即可。我已在下图中标出该位置。

已设置为美国电话号码格式的 Excel 列
已设置为美国电话号码格式的 Excel 列

最后,在该列中输入一个公式,把刚刚清理过的电话号码填入针对美国号码优化的新列。

在这个仅用于已审核美国号码的显示示例中,=VALUE(D3) 会向显示列提供数值。D3 本身应继续以文本形式保留。仅使用 =D3 这样的引用仍会返回文本,因此不会应用数字型电话号码显示格式。

将电话号码填入新列的简单公式
将电话号码填入新列的简单公式

输入公式后,按“ENTER”应用。

如果所有操作都正确,结果应如下所示:

Excel 中已格式化的美国电话号码
Excel 中已格式化的美国电话号码

然后向下拖动右下角的填充柄,将格式应用到 Excel 中的所有电话号码,如下图所示:

以美国格式显示所有电话号码
以美国格式显示所有电话号码

清理公司名称法律后缀并规范大小写

在开始向潜在客户开展 Outbound 之前,清理公司名称可能是最重要的数据准备工作之一,因此这部分不能省略。

请采用一条能够清楚解释的后缀规则。SUBSTITUTE 会替换出现在任意位置的匹配文本。例如,=SUBSTITUTE("Acme Consulting"," Co","") 会返回 Acmensulting,误删真实名称的一部分。匹配区分大小写:如果改用 " co",除非先将源数据转成小写,否则 Acme Consulting 不会发生变化。连续执行多个替换时,结果还会受到替换顺序影响。因此,不建议将这种宽泛的替换链直接应用到 CRM 数据列。

如果已确认的规则是删除名称末尾的一个空格及其后的 LLC,请使用:

=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))

这是一条有意收窄范围的规则。Acme LLC 会变成 Acme,而 Acme LLC Services 和 Acme Consulting 保持不变。它无法处理 L.L.C.、所有法律实体形式或每个国家和地区的公司后缀。如果数据中存在其他情况,请分别添加经过检查的规则。

下方原有截图展示的是旧版宽泛替换示例。最终输出应使用上面的明确规则,并在接受改动前与原始公司名称进行对比。

将公司名称转为小写并删除后缀的公式
将公司名称转为小写并删除后缀的公式

范围更窄的公式如何工作:TRIM 删除首尾的普通空格,RIGHT 检查最后四个字符,LEFT 只删除已经确认的结尾。在常规 Excel 公式中,该比较不区分大小写。不要默认 TRIM 能删除所有 Unicode 空白字符。

公司名称清理公式应用后的效果如下:

应用公司名称清理公式
应用公司名称清理公式

不要忘记向下拖动原公式单元格右下角的填充柄,将公式应用到所有公司名称,如下图所示:

将公司名称清理公式应用到整个列表
将公司名称清理公式应用到整个列表

但还有一个问题:这些公司名称尚未按首字母大写格式显示。

只有当标题式大小写确实是预期输出时,才使用 =PROPER(D4)。它会把 IBM 改成 Ibm,因此在品牌官方写法很重要时,应保留原有大小写或添加明确的例外规则。截图仅用于展示转换效果,并不保证结果就是正确的公司或品牌名称。

将公司名称按专有名词格式大写的公式
将公司名称按专有名词格式大写的公式

正确完成后的效果如下:

应用公司名称大小写转换公式
应用公司名称大小写转换公式

最后,向下拖动右下角的填充柄,将公式应用到整个公司名称列表。

将公式应用到所有公司名称
将公式应用到所有公司名称

如果你已经有一份审核通过的新旧名称对照表,可以按照批量替换多个值的操作流程应用这些映射关系。

如果公司名称的情况更加复杂,可以参考使用 AI 清理公司名称的指南。无论选择哪种方法,都应保留原始值,并检查法律实体后缀、缩写和存在歧义的名称。

常见问题

如何在 Excel 中从邮箱地址提取域名?

在新版 Excel 中,可以使用 TEXTAFTER 函数;在旧版本中,则可以组合使用 RIGHT、LEN 和 FIND 函数。

Excel 清理混乱电话号码的最佳方法是什么?

将电话号码输入值保留为文本,只删除明确需要移除的标点,并检查前导零、国家或地区代码及分机号。数字显示格式只是一个独立且可选的美国号码示例,不具备验证功能,也不等同于国际电话号码标准化。

Excel 能否格式化美国以外国家的电话号码?

不要把美国号码显示示例应用到包含多个国家或地区的列表。请保留原始文本和国家或地区信息,再选择明确的国际标准化规则或合适的电话号码处理工具。仅调整格式无法证明号码有效。

如何在 Excel 中删除 LLC、Inc 和 Ltd 等公司后缀?

首先采用经过检查的末尾后缀规则,例如只删除名称结尾的 LLC。不要在名称内部执行不受限制的子字符串替换。大小写处理应作为单独的决策,因为 PROPER 会改变缩写和品牌的官方大小写。

Excel 没有 TEXTAFTER 函数怎么办?

可以使用兼容旧版 Excel 的替代公式,例如组合使用 RIGHT、LEN 和 FIND 函数;也可以检查当前 Excel 版本是否支持 TEXTAFTER。