使用Excel公式/VBA清洗字典格式数据 提取文本替换分隔符

针对表格中Language、Product两列的特殊格式单元格（内容规则：单条数据为数字ID;#文本描述，多条数据之间用分号;拼接），以下两种方案均可实现「移除所有ID前缀、保留纯文本、多值分隔符替换为逗号」的清洗需求，无需借助Python等外部工具。

方案1：原生公式实现（免宏，适配Excel 365/2021及以上版本） #

• 假设原始数据首行从第2行开始，Language列为A列、Product列为B列，在空白结果列首行（如C2单元格）输入以下公式，按回车后下拉填充，即可得到Language列的清洗结果；将公式中A2替换为B2，同操作即可得到Product列清洗结果：

=TEXTJOIN(",",TRUE,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,";#","</s><s>"),";","</s><s>")&"</s></t>","//s[position() mod 2=0]"))

• 公式逻辑说明：
  • 两层SUBSTITUTE替换将原始字符串的所有分隔符转换为XML结构化节点标记
  • FILTERXML筛选偶数位置的节点内容，自动跳过所有奇数位的数字ID
  • TEXTJOIN将筛选出的有效文本用逗号拼接，自动忽略空值，避免出现多余逗号

方案2：VBA自定义函数实现（全Excel版本兼容，操作更简便） #

如果使用的是2019及更早的Excel版本，或是需要频繁处理同类数据，可使用自定义函数方案：

• 按Alt+F11快捷键打开VBA编辑器，在左侧工程资源管理器中右键点击当前工作簿名称，依次选择「插入」-「模块」
• 在弹出的模块代码编辑区粘贴以下代码：

Function CleanCellData(rawText As String) As String
    Dim splitArr As Variant, i As Long, resultStr As String
    splitArr = Split(rawText, ";")
    resultStr = ""
    For i = LBound(splitArr) To UBound(splitArr)
        If InStr(splitArr(i), ";#") > 0 Then
            ' 截取;#分隔符后的纯文本内容
            resultStr = resultStr & Mid(splitArr(i), InStr(splitArr(i), ";#") + 2) & ","
        End If
    Next
    ' 移除末尾多余逗号
    If Len(resultStr) > 0 Then
        CleanCellData = Left(resultStr, Len(resultStr) - 1)
    Else
        CleanCellData = ""
    End If
End Function

• 粘贴完成后关闭VBA编辑器回到Excel界面，直接在结果单元格输入=CleanCellData(A2)，下拉填充即可完成清洗，Language、Product两列均可直接调用该函数，无需调整代码。

校验提示：清洗完成后可抽查多值单元格，确认无ID残留、无多余首尾逗号、分隔符统一为英文逗号即可。

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