跳到主要內容

如何從Excel中的名稱中提取姓名縮寫？

當您處理客戶記錄或進行郵件合併時，從給定名稱獲取縮寫可能對您有用，例如，HG for Hal Greer。 因此，在這裡我為您介紹了一些技巧，可幫助您從Excel的名稱列表中快速提取縮寫。

在Excel中使用公式提取縮寫

在Excel中使用定義的函數提取縮寫

箭頭藍色右氣泡 在Excel中使用公式提取縮寫

第一種方法是使用公式從Microsoft Excel中的名稱提取縮寫。

選擇一個空白單元格，例如，單元格C2，鍵入此公式 =LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")（A2是您的姓名列表的第一個單元格），按 Enter 按鈕，然後拖動填充手柄以填充範圍。 現在，您會看到每個名稱的所有縮寫均從名稱列表中提取。

箭頭藍色右氣泡 在Excel中使用定義的函數提取縮寫

除公式外，您還可以使用“定義的函數”在Microsoft Excel中輕鬆地從指定名稱中提取縮寫。

1.選擇要選擇的列的單元格，然後按 Alt + F11鍵 打開 Microsoft Visual Basic for Applications 窗口。

2.在彈出窗口中，單擊 插入 > 模塊，然後將以下VBA代碼粘貼到模塊中。

VBA：從名稱中提取縮寫

Function FirstCharacters(pWorkRng As Range) As String
'Updateby20140514
Dim arr As Variant
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
arr = VBA.Split(Trim(xValue))
For i = 0 To UBound(arr)
    OutValue = OutValue & VBA.Left(arr(i), 1) & "."
Next
FirstCharacters = OutValue
End Function

3.保存代碼並關閉窗口，選擇一個空白單元格並鍵入此公式 = FirstCharacters（A2），然後按 Enter 按鈕，然後拖動填充手柄以填充所需的範圍。 之後，您可以看到每個名稱的縮寫都已提取。

尖端：您可以更改分隔符 “。” 您可以在上述VBA中選擇首字母縮寫。

相關文章：

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (13)
No ratings yet. Be the first to rate!
Gary
Gary
This comment was minimized by the moderator on the site
Hello,
I need to extend this code to 4 initials, but I don’t understand how to extend it.

=LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

Please can you explain how to add another layer to this formula?
Reply
0
0
Sunny
Sunny     Gary
This comment was minimized by the moderator on the site
Hi, use the formula like this:
=LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2,FIND(" ",A2)+1)+1)+1,1),"")

About the explaination, it not few words can explan. You may know what the functions in the formula is used for what destination. If you want to learn the function, you can visit this site: https://www.extendoffice.com/excel/functions.html
Reply
Report
0
0
Gary
Gary     Sunny
This comment was minimized by the moderator on the site
Thank you so much. This worked perfectly.
Reply
0
0
A.B. Mati
A.B. Mati
This comment was minimized by the moderator on the site
If u need the prefix of Mr., Ms. or Mrs. can add it in the beginning as =LEFT(A2,SEARCH(" ",A2))&
Reply
0
0
A.B. Mati
A.B. Mati
This comment was minimized by the moderator on the site
Very good formula to get the initials from a name. But if the name contains with Mr., Ms. or Mrs. then this does not work. We have to separate put this prefix first. For that you can replace the A2 with TRIM(MID(A2,SEARCH(" ",A2)+1,100)) . So the formula will be as under
=LEFT(TRIM(MID(A2,SEARCH(" ",A2)+1,100)) )&IF(ISNUMBER(FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)) )),MID(TRIM(MID(A2,SEARCH(" ",A2)+1,100)),FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)) )+1,1),"")&IF(ISNUMBER(FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)),FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)) )+1)),MID(TRIM(MID(A2,SEARCH(" ",A2)+1,100)),FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)),FIND(" ",TRIM(MID(A2,SEARCH(" ",A2)+1,100)) )+1)+1,1),"")
Reply
0
0
Stitch
Stitch
This comment was minimized by the moderator on the site
Thank you for this!! I had been struggling with getting initials from a last name with a space or hyphen and was able to use this formula as-is, with the addition of another IF that searched for a hyphen. A huge relief!!
Reply
Report
0
0
Benjamin Robinson
Benjamin Robinson
This comment was minimized by the moderator on the site
I used the formula above, but if the person does not put a space between their first / last name, how do you keep that from error out?
Reply
0
0
Ming Quan
Ming Quan
This comment was minimized by the moderator on the site
Amazing Thank you!
Reply
0
0
Kasun D
Kasun D
This comment was minimized by the moderator on the site
hi there
I Want to sort full name in to a new format if excel cell has text character more than 25 how to show it in initials and last name
Ex 01:- name has more than 25 characters
Full Name : Mildred Dresselhaus Lene Vestergaard Hau
Show as : M.D.L Vestergaard Hau
(it may add if more initials are ther for a long name)
Ex 02:-
Full Name : Enrico Fermi
Show as : Enrico Fermi
Reply
0
0
abedir
abedir
This comment was minimized by the moderator on the site
If the text contains more than 03 words
Reply
0
0
JD
JD
This comment was minimized by the moderator on the site
This works!!! Thank you!
Reply
0
0
Cristina
Cristina
This comment was minimized by the moderator on the site
Thank you! :lol: quite useful!
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations