从一个单元格中提取多个名字、姓氏和中间名

这基本上是一个小位字符串处理，函数 Split 是关键。你只需要知道它返回一个 0 索引的字符串数组。

因此，首先将字符串拆分以获得所有人员的列表。现在循环遍历人称并将该名称拆分，以将姓氏与名字（和可选中间名）分开。":"","

使用您选择的分隔符将这些名称粘合在一起。

以下函数可以用作 UDF：

Function getNames(S As String, getSurnames As Boolean) As String
    Const NameSeparator = ":"
    Const SurnameSeparator = ","
    Const ResultSeparator = ";"
    
    Dim allPersons() As String
    ' First we create an array with one entry per person
    allPersons = Split(S, NameSeparator)
    
    ' Decide if we want to have first or second piece of the name
    Dim namePartIndex As Long
    namePartIndex = IIf(getSurnames, 0, 1)
    
    Dim i As Long
    For i = 0 To UBound(allPersons)
        ' Split the name of a person.
        Dim nameparts() As String
        nameparts() = Split(allPersons(i), SurnameSeparator)
        If UBound(nameparts) >= namePartIndex Then
            getNames = getNames & IIf(getNames = "", "", ResultSeparator) & Trim(nameparts(namePartIndex))
        End If
    Next
End Function

现在，您所要做的就是将公式放入 X 列和 Y 列中：

=getNames(O5, getSurnames:=False)  ' For Firstnames (and Middle Names)
=getNames(O5, getSurnames:=True)   ' For Surnames

更新：很抱歉造成混淆，从 Excel 调用函数时不允许使用命名参数。这应该是

=getNames(O5, False)  ' For Firstnames (and Middle Names)
=getNames(O5, True)   ' For Surnames