☰

組合名字的前兩個字母和姓氏的前兩個字母

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
組合名字的前兩個字母和姓氏的前兩個字母

我有一個用戶名電子表格。

名字和姓氏位於 column 的同一單元格中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()ie替換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的解決方案修改為也適用於單一名稱（在這種情況下，結果只是名稱的前兩個字元）。


筆記：如果輸入的話，美化的公式實際上會起作用。

相關內容