excel - 使用 3 个单元的 UDF

Question

我正在尝试将以下公式转换为 UDF，主要是为了让人们更容易使用。

=IF(LEFT(TRIM(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)),4)="and ",RIGHT(TRIM(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)),LEN(TRIM(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)))-4),TRIM(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)))

该公式只是一个有条件的文本删除器，但它对于有一个可见的过程是必要的。 A2 是一个名称列表，用逗号或“和”分隔，B 和 C2 是我们从字符串左侧删除的前一个名字和姓氏。

我已经完成了其中的一些，但这是第一个使用多个单元的方法，并且我之前使用的相同方法不起作用。我已更改位以使用 Application.WorksheetFunction 并将单元格引用更改为 A、B 和 C.Value2，但我似乎无法使其正常工作。

Public Function NextName(A As Range, B As Range, C As Range)

    NextName = _
        Application.WorksheetFunction.IF(Left(Trim(Right(A.Value2, _
        Application.WorksheetFunction.Len(A.Value2) - Application.WorksheetFunction.Len(B.Value2) - _
        Application.WorksheetFunction.Len(C.Value2) - 2)), 4) = "and ", Right(Trim(Right(A.Value2, Application.WorksheetFunction.Len(A.Value2) - _
        Application.WorksheetFunction.Len(B.Value2) - Application.WorksheetFunction.Len(C.Value2) - 2)), _
        Application.WorksheetFunction.Len(Trim(Right(A.Value2, Application.WorksheetFunction.Len(A.Value2) - _
        Application.WorksheetFunction.Len(B.Value2) - Application.WorksheetFunction.Len(C.Value2) - 2))) - 4), Trim(Right(A.Value2, _
        Application.WorksheetFunction.Len(A.Value2) - Application.WorksheetFunction.Len(B.Value2) - Application.WorksheetFunction.Len(C.Value2) - 2)))

End Function

[例子]

(A2)

John Smith Rogerson and Jane Laura Manson, Name2 Name2 Name2, Name3 Name3 Name3, Name4 Name4 Name4

(B2)

John Smith Rogerson

(C2)

Rogerson

(结果)

Jane Laura Manson, Name2 Name2 Name2, Name3 Name3 Name3, Name4 Name4 Name4

Answer 1

您可以将其分解并使用 VBA函数而不是 Worksheet让它变得简单

Public Function NextName(A As Range, B As Range, C As Range) As String
    ' LEFT(TRIM(RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)),4)="and "
    If Left(Trim(Right(A.Value2, Len(A.Value2) - Len(B.Value2) - Len(C.Value2) - 2)), 4) = "and " Then
        ' RIGHT(TRIM(RIGHT(A17,LEN(A17)-LEN(B17)-LEN(C17)-2)),LEN(TRIM(RIGHT(A17,LEN(A17)-LEN(B17)-LEN(C17)-2)))-4)
        NextName = Right(Trim(Right(A.Value2, Len(A.Value2) - Len(B.Value2) - Len(C.Value2) - 2)), Len(Trim(Right(A.Value2, Len(A.Value2) - Len(B.Value2) - Len(C.Value2) - 2))) - 4)
    Else
        ' TRIM(RIGHT(A17,LEN(A17)-LEN(B17)-LEN(C17)-2))
        NextName = Trim(Right(A.Value2, Len(A.Value2) - Len(B.Value2) - Len(C.Value2) - 2))
    End If
End Function

我怀疑您也可以在这里进行很多简化，但是如果没有示例数据，我将无法扩展。

刚刚看到您的示例数据，这可能是我处理您的场景的方式。你不需要细胞 B 或 C 这样做。

Public Function NextName(A As Range) As String
    Dim nmes As Variant, tmp As Variant
    Dim i As Long
    ' Split string into array on comma delimiter
    nmes = Split(A.Value2, ",")
    For i = LBound(nmes) To UBound(nmes)
        tmp = Empty
        ' Test if "and" in the string
        If InStr(nmes(i), " and ") Then
            ' Split string using " and " as delimiter
            tmp = Split(nmes(i), " and ")
            ' Return only last value
            nmes(i) = Trim(tmp(UBound(tmp)))
        ' This isn't really needed but I've left it in in case you wanted to process any strings that didn't have " and " in them.
        Else
            nmes(i) = Trim(nmes(i))
        End If
    Next i
    ' Return values        
    NextName = Join(nmes, ", ")
End Function