当你需要在导入 CRM 前提取邮箱域名、统一电话号码文本并检查公司名称时,可以使用这些清洗销售数据的 Excel 公式。请保留原始列,并将每个处理结果写入新列,方便核对具体改动。
我们先来看几类棘手的数据:以 0 开头的电话号码、缺少邮箱分隔符的地址,以及包含类似法律实体后缀字样的公司名称。只在第一行整洁数据上有效的公式,向下填充后可能会破坏这些特殊值。
本指南将介绍三项操作:
📌 时间有限?先看摘要
通过三个针对性的 Excel 转换,为经过检查的 CRM 导入整理销售数据。将源数据与处理结果并排保留,并在替换任何内容之前检查异常值。
- 输入: 邮箱地址、电话号码文本和公司名称。
- 输出: 提取的域名、删除指定标点后的电话号码文本,以及按照明确后缀规则清理的公司名称。
从邮箱地址提取域名
在 Excel 中,从邮箱地址列表提取域名有两种方法,下面分别说明。
第一种:使用 TEXTAFTER 函数。该函数适用于 Microsoft 365、Excel 网页版和 Excel 2024。请查看 Microsoft 的最新函数文档,确认你的版本是否支持。
第二种:组合使用 RIGHT、LEN 和 FIND 函数——适合无法使用 TEXTAFTER 的用户。
按照以下步骤从邮箱地址中提取域名:
- 在公式栏中输入
=TEXTAFTER - 选择包含第一个邮箱地址的单元格,例如 B3
- 将
@添加为查找文本
完整的 Excel 公式如下:
=TEXTAFTER(B3,"@")
要将公式应用到所有邮箱地址,请拖动公式单元格右下角的填充柄。
在旧版 Excel 中提取邮箱域名
接下来介绍如何在不支持 TEXTAFTER 函数的旧版 Excel 中提取邮箱域名。
我们要使用的公式是 =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。
要将该公式应用到所有单元格,请向下拖动原公式单元格右下角的填充柄。
如果你更习惯使用 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”格式。
选择“Phone Numbers”以及电话号码所属的国家或地区。
💡 Excel 数字格式只能改变数值单元格的显示方式,无法补充缺失的国家或地区代码,也不能验证电话号码。对于国际号码列表,请保留文本值并检查国家或地区信息,再选择专门的电话号码清洗流程。
现在关闭设置面板,并确认 Excel 是否已应用该格式。
只需查看格式栏中是否显示“Special”即可。我已在下图中标出该位置。
最后,在该列中输入一个公式,把刚刚清理过的电话号码填入针对美国号码优化的新列。
在这个仅用于已审核美国号码的显示示例中,=VALUE(D3) 会向显示列提供数值。D3 本身应继续以文本形式保留。仅使用 =D3 这样的引用仍会返回文本,因此不会应用数字型电话号码显示格式。
输入公式后,按“ENTER”应用。
如果所有操作都正确,结果应如下所示:
然后向下拖动右下角的填充柄,将格式应用到 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 清理公司名称的指南。无论选择哪种方法,都应保留原始值,并检查法律实体后缀、缩写和存在歧义的名称。




















