☰

合并名字的前两个字母和姓氏的前两个字母

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
合并名字的前两个字母和姓氏的前两个字母

我有一个用户名电子表格。

名字和姓氏位于列的同一单元格中A。

是否有一个公式可以将名字的前两个字母（第一个单词）和姓氏的前两个字母（第二个单词）连接起来？

例如John Doe应该变成JoDo。

我试过

=LEFT(A1)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))-IFERROR(FIND(" ",A1),LEN(A1)))

但这给了我JoDoe结果。

答案1

是的；假设每个人只有名字和姓氏，并且它们之间总是用空格隔开，您可以使用以下内容：

=LEFT(A1,2)&MID(A1,SEARCH(" ",A1)+1,2)

我只能根据这些假设来回答这个问题，因为这就是您所提供的全部内容。

或者如果您希望仍然包含空格：

=LEFT(A1,2)&" "&MID(A1,SEARCH(" ",A1)+1,2)

答案2

最后，这里有一个解决方案，它将返回名字的前两个字符，以及姓氏的前两个字符，但也解释了中间名。

=LEFT(A1,2)&LEFT(MID(A1,FIND("~~~~~",SUBSTITUTE(A1," ","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)),2)

在此处输入图片描述

谢谢@Kyle 给出公式的主要部分

答案3

这是另一种方法...

工作表截图

  • 一个名字
  • B-=CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))

答案4

基于这个答案，这里有一个优雅的解决方案，可以处理任意数量的中间名：

=LEFT(A1,2)&LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),2)

解释：

SUBSTITUTE(A1, " ", REPT(" ",LEN(A1)))将单词间空格替换为与整个字符串长度相等的空格。使用字符串长度而不是任意大的数字可确保公式适用于任何长度的字符串，并且意味着它可以高效地完成。

RIGHT(space_expanded_string, LEN(A1))提取最右边以一串空格开头的单词。*

TRIM(space_prepended_rightmost_word)提取最右边的单词。

LEFT(rightmost_word, 2)提取最右边单词（姓氏）的前两个字符。


*警告：如果用户名可能包含尾随空格，则需要将 的第一个参数SUBSTITUTE()（即 ）替换A1为TRIM(A1)。前导空格和单词之间的多个连续空格仅使用 即可正确处理A1。

修复你的尝试

仔细查看您尝试的解决方案，看起来您已经非常接近一个可行的公式，即将第一个单词（即名字）的前两个字母和第二单词是否存在。

请注意，如果用户名包含中间名，则更正后的公式会错误地从第一个中间名而不是姓氏中获取前两个字母（假设您的意图确实是从姓氏中提取它们）。

此外，如果所有用户名仅由名字或名字和姓氏组成，则公式不必要地复杂，可以简化。


要了解公式如何工作并进行修复，如果将其美化会更容易，如下所示：

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  IFERROR(
    FIND(" ", SUBSTITUTE(A1," ","",1)),
    LEN(A1)
  )
  - IFERROR(FIND(" ",A1), LEN(A1))
)


要了解其工作原理，首先看看当A1不包含空格时会发生什么（即它仅包含一个名称）。所有函数都IFERROR()对其第二个参数进行评估，因为如果在目标字符串中找不到搜索字符串，FIND()则会返回错误：#VALUE!

=
LEFT(A1,2) &
MID(
  A1,
  LEN(A1) + 1,
  LEN(A1)
  -LEN(A1)
)

的第三个参数MID()计算结果为零，因此函数输出""且公式结果是单个名称的前两个字符。


现在看看当恰好有两个名称时（即恰好有一个空格）。第一个和第三个IFERROR()函数求值它们的第一个参数，但第二个函数求值它的第二个参数，因为它FIND(" ", SUBSTITUTE(A1," ","",1))在删除第一个（也是唯一一个）空格后尝试寻找另一个空格：

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  LEN(A1)
  - FIND(" ",A1)
)

显然，MID()返回第二个单词（即姓氏）的全部内容，公式结果是名字的前两个字符，后面跟着全部姓氏的字符。


为了完整起见，我们还将研究至少有三个名字的情况，尽管现在应该很明显如何修复公式。这一次，所有函数都IFERROR()对其第一个参数求值：

=
LEFT(A1,2) &
MID(
  A1,
  FIND(" ",A1) + 1,
  FIND(" ", SUBSTITUTE(A1," ","",1))
  - FIND(" ",A1)
)

它比前一种情况稍微不那么清晰，但MID()返回的是整个第二单词（即第一个中间名）。因此，公式结果是名字的前两个字符，后跟第一个中间名的所有字符。


显然，解决方法是LEFT()获取输出的前两个字符MID()：

=
LEFT(A1,2) &
LEFT(
  MID(
    A1,
    IFERROR(FIND(" ",A1), LEN(A1)) + 1,
    IFERROR(
      FIND(" ", SUBSTITUTE(A1," ","",1)),
      LEN(A1)
    )
    - IFERROR(FIND(" ",A1), LEN(A1))
  ),
  2
)


我上面提到的简化方法是替换LEFT(MID(…,…,…), 2)为MID(…,…,2)：

=
LEFT(A1,2) &
MID(
  A1,
  IFERROR(FIND(" ",A1), LEN(A1)) + 1,
  2
)

或者在一行中：

=LEFT(A1,2)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,2)

这本质上PeterH 的解决方案经过修改，也适用于单个名称（在这种情况下，结果只是名称的前两个字符）。


笔记：美化的公式如果输入的话确实有效。

相关内容