Excel下基于';'分隔符从文本生成单细胞列表 保留X400内单分号

适用场景说明 #

需要从单个单元格存储的字符串中提取SMTP、smtp、X400、FAX四类地址，生成单个单元格内的换行列表：

• 不同地址条目原本计划用单分号;分隔，但X400类地址内部包含单分号需要完整保留
• 不同地址条目之间的实际分隔标记为双分号;;，所有地址均以对应类别名+冒号作为开头标识

公式方案（适用于Excel 365/2021、WPS最新版） #

直接在输出单元格输入如下公式，假设输入内容存储在A2单元格：

=TEXTJOIN(CHAR(10),TRUE,
  FILTER(
    TEXTSPLIT(REGEXREPLACE(A2,"(SMTP:|smtp:|X400:|FAX:)","|$1"),"|"),
    TEXTSPLIT(REGEXREPLACE(A2,"(SMTP:|smtp:|X400:|FAX:)","|$1"),"|")<>""
  )
)

输入后开启输出单元格的「自动换行」功能，即可看到每行一条地址的列表效果。

公式逻辑说明 #

1. 用REGEXREPLACE给所有地址类别的前缀前统一添加特殊分隔符|，彻底避免和X400内部的单分号混淆
2. 用TEXTSPLIT按|拆分得到所有独立地址条目
3. 用FILTER过滤拆分产生的空值
4. 用TEXTJOIN配合换行符CHAR(10)把所有条目拼接为单个单元格内的换行列表

自定义函数方案（适用于所有支持VBA的Excel版本） #

如果使用的是低版本Excel，可通过VBA自定义函数实现：

1. 按Alt+F11打开VBA编辑器，右键点击当前工作簿名称选择「插入」-「模块」
2. 在模块编辑框粘贴如下代码：

Function SplitAddress(ByVal inputStr As String) As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim result As String
    
    Set regex = CreateObject("VBScript.RegExp")
    regex.Global = True
    regex.Pattern = "(SMTP:|smtp:|X400:|FAX:)[^;]*(?:;(?!SMTP:|smtp:|X400:|FAX:)[^;]*)*"
    
    Set matches = regex.Execute(inputStr)
    For Each match In matches
        result = result & match.Value & vbCrLf
    Next
    If Len(result) > 0 Then result = Left(result, Len(result) - 2)
    SplitAddress = result
End Function

3. 回到工作表，在输出单元格输入=SplitAddress(A2)即可得到结果。

效果验证 #

以提供的输入样例测试，拆分后所有X400地址内部的单分号完全保留，四类地址均被正确拆分，每行一条展示在单个单元格内。

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