excel - 分割前缀和姓氏(基于数组数据)

Question

我有一列由姓名组成的数据，其中可能包含也可能不包含姓氏前缀。这些前缀可以存在于多个单词中。我有所有可能的前缀的列表，但现在我需要拆分前缀和姓氏，并用数据制作 2 列。

我所做的是编写如下的 Excel 公式:

=IF(
   RIGHT(A1;7) = " van de"
 ;
   RIGHT(A1;6)
 ;
   IF(
     RIGHT(A1;4) = " van"
   ;
     RIGHT(A1;3)
   ;
     IF(
       RIGHT(A1;3) = " de"
     ;
       RIGHT(A1;2)
     ;
       --Insert more nested If statements here--
     )
   )
 )

姓氏数据可能如下所示:

Name1 van de
Name1 van
Name1
Name1 Name2 van
Name1-Name2 Name3 van de
Name1 Name2 Name3

我想要什么:

  OriginalName   |  Name  |  Prefix
-----------------|--------|----------
      a b        |   a    |    b
      a b c      |   a    |    b c

首先，这是一种相当低效的方法，但我自动创建了这个公式，所以这不再是问题了。现在我发现嵌套的 If 语句是有限制的，我必须超过这个限制。

我该如何解决这个问题？

我有一个包含可能前缀的数组。也许这会有帮助？

Answer 1

我假设您想要将“van”和“de”前缀与名称的其余部分分开。如果我误解了，请提供更多有关您的问题的示例...

以下解决方案需要一个辅助列来确定“前缀”的起始位置，但您可以根据需要隐藏它:

首先，将我的值放入 A8:A9 (van; de) 中的任何位置，并将其命名为 prefix，以便可以在以下公式。

C1 中的公式是一个数组公式(使用 Ctrl+Shift+输入):

=MIN(IF(ISNUMBER(SEARCH(prefix,A1)),SEARCH(prefix,A1)))

D1和E1中的公式或普通公式:

=IF(C1>0,LEFT(A1,C1-2),A1)
=IF(C1>0,MID(A1,C1,LEN(A1)),"")