วิธีแยกชื่อและนามสกุลใน Excel
คุณสามารถใช้ Excel เพื่อแบ่งข้อมูลออกเป็นชิ้นเล็กๆ การค้นหาข้อมูลที่คุณต้องการและจัดการข้อมูลนั้นเป็นเป้าหมายสำคัญสำหรับผู้ใช้ Excel หลายๆ คน
หากคุณมีชื่อเต็มของบุคคล คุณอาจต้องเน้นแค่ชื่อหรือนามสกุลของบุคคลนั้น ตัวอย่างเช่น หากคุณกำลังส่งอีเมลอัตโนมัติที่เป็นมิตรไปยังลูกค้าของคุณ คุณต้องใช้ชื่อของพวกเขาเพื่อหลีกเลี่ยงไม่ให้ฟังดูไม่มีตัวตน หากคุณกำลังมองหารายชื่อผู้ตอบแบบสำรวจ อาจเป็นเรื่องสำคัญที่ต้องใช้นามสกุลหรือซ่อนนามสกุลเพื่อไม่ให้เปิดเผยชื่อ
Excel ทำให้กระบวนการนี้ตรงไปตรงมา และมีวิธีการต่างๆ มากมายที่คุณสามารถทำได้ นี่คือบทช่วยสอนที่จะช่วยคุณสร้างคอลัมน์ชื่อและนามสกุลแยกจากกันโดยใช้สูตร เราครอบคลุมปัญหาของชื่อกลางเช่นกัน
สูตร Excel สำหรับการแยกชื่อเป็นส่วนๆ
คุณจะเริ่มต้นที่ไหน
การแยกชื่อ
นี่คือสูตรทั่วไป:
=LEFT(เซลล์,FIND(” “,เซลล์,1)-1)
ในการดำเนินการให้แทนที่ เซลล์ ด้วยตัวชี้เซลล์ที่มีชื่อเต็มแรกที่คุณต้องการแยกออก ในตัวอย่างนี้ คุณต้องการเลือก B2 และป้อนสูตร:
=LEFT(A2,FIND(” “,A2,1)-1)
อย่างไรก็ตาม สิ่งสำคัญที่ควรทราบคือ ในบางอุปกรณ์ สูตรนี้ใช้เครื่องหมายอัฒภาคแทนเครื่องหมายจุลภาค ดังนั้น หากสูตรข้างต้นใช้ไม่ได้ผล คุณอาจต้องใช้เวอร์ชันต่อไปนี้แทน:
=LEFT(เซลล์;FIND(” “;cell;1)-1)
ในตัวอย่าง คุณจะใช้:
=LEFT(A2;FIND(” “;A2;1)-1)
ตอนนี้คุณสามารถลากที่จับเติมลงไปที่ส่วนท้ายของคอลัมน์ชื่อได้
ฟังก์ชัน LEFT ให้คุณแยกสตริงโดยเริ่มจากด้านซ้ายสุดของข้อความ ส่วน FIND ของสูตรนี้จะระบุตำแหน่งช่องว่างแรกในชื่อเต็ม ดังนั้นคุณจะได้ส่วนของชื่อเต็มของคุณที่อยู่ก่อนพื้นที่ว่าง
ดังนั้น ชื่อที่ใส่ยัติภังค์จะอยู่ด้วยกัน และชื่อแรกที่มีอักขระพิเศษก็เช่นกัน แต่คอลัมน์ชื่อเต็มของคุณจะไม่มีชื่อกลางหรือชื่อกลาง
จุลภาคหรือเซมิโคลอน?
ทำไมสูตรไม่เหมือนกันสำหรับทุกคน?
สำหรับผู้ใช้ Excel จำนวนมาก ฟังก์ชัน Excel จะใช้เครื่องหมายจุลภาคเพื่อแยกข้อมูลที่ป้อนเข้า แต่ในอุปกรณ์บางเครื่อง การตั้งค่าภูมิภาคจะแตกต่างกัน
หากต้องการค้นหาว่า Excel ของคุณใช้สัญลักษณ์ใด ให้เริ่มพิมพ์ในสูตร เมื่อคุณเริ่มป้อน =ซ้าย(คุณจะเห็นข้อความโฮเวอร์ที่จะแนะนำการจัดรูปแบบที่ถูกต้อง
การแยกนามสกุล
ใช้วิธีการเดียวกันในการแยกนามสกุล คราวนี้ คุณควรใช้สูตร RIGHT ซึ่งแยกสตริงที่เริ่มจากด้านขวา
สูตรที่คุณต้องการคือ:
=RIGHT(เซลล์, LEN(เซลล์) – SEARCH(“#”, SUBSTITUTE(cell,” “, “#”, LEN(cell) – LEN(SUBSTITUTE(cell, ” “, “”))))
ในตัวอย่างข้างต้น คุณจะใช้สูตรต่อไปนี้ในเซลล์ C2:
=RIGHT(A2, LEN(A2) – SEARCH(“#”, SUBSTITUTE(A2,” “, “#”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”))))
อีกครั้ง คุณอาจต้องเปลี่ยนจากเครื่องหมายจุลภาคเป็นอัฒภาค ซึ่งหมายความว่าคุณอาจต้องใช้:
=RIGHT(A2; LEN(A2) – SEARCH(“#”; SUBSTITUTE(A2;” “; “#”; LEN(A2) – LEN(SUBSTITUTE(A2; ” “; “”)))))
ยัติภังค์นามสกุลและนามสกุลที่มีอักขระพิเศษยังคงไม่บุบสลาย
เหตุใดสูตรนี้จึงซับซ้อนกว่าสูตรสำหรับชื่อจริง การแยกชื่อกลางและชื่อกลางออกจากนามสกุลยากกว่า
ถ้าคุณต้องการให้ชื่อกลางและชื่อย่อแสดงพร้อมกับนามสกุล คุณสามารถใช้สูตรได้:
=RIGHT(เซลล์, LEN(เซลล์) – SEARCH(” “, เซลล์))
หรือ:
=ขวา(A2, LEN(A2) – ค้นหา(” “, A2))
หรือ:
=RIGHT(A2; LEN(A2) – SEARCH(” “; A2))
แต่ถ้าคุณต้องการแยกชื่อกลางล่ะ? สิ่งนี้ไม่ธรรมดา แต่ก็มีประโยชน์ที่จะรู้
การแยกชื่อกลาง
สูตรสำหรับชื่อกลางมีดังต่อไปนี้:
=MID(เซลล์, SEARCH(” “, เซลล์) + 1, SEARCH(” “, เซลล์, SEARCH(” “, เซลล์)+1) – SEARCH(” “, เซลล์)-1)
ในตัวอย่างข้างต้น คุณจะได้รับ:
=MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1)
ถ้า Excel ของคุณใช้อัฒภาค สูตรจะเป็นดังนี้:
=MID(A2; SEARCH(” “; A2) + 1; SEARCH(” “; A2; SEARCH(” “; A2)+1) – SEARCH(” “; A2)-1)
หลังจากป้อนสูตรแล้ว ให้ลากที่จับเติมลงไป นี่คือคอลัมน์ชื่อกลางที่เพิ่มไปยังตัวอย่างด้านบน:
หากชื่อเต็มไม่มีชื่อกลางหรือชื่อย่อ คุณจะได้รับค่าศูนย์ในคอลัมน์นี้ ซึ่งอาจแสดงเป็น #VALUE! ในการรับเซลล์ว่างแทนที่ #VALUE! คุณสามารถใช้ฟังก์ชัน IFERROR
จากนั้นสูตรของคุณจะกลายเป็น:
=IFERROR(MID(เซลล์, SEARCH(” “, เซลล์) + 1, SEARCH(” “, เซลล์, SEARCH(” “, เซลล์)+1) – SEARCH(” “, เซลล์)-1),0)
หรือ:
=IFERROR(MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1),0)
หรือ:
=IFERROR(MID(A2; SEARCH(” “; A2) + 1; SEARCH(” “; A2; SEARCH(” “; A2)+1) – SEARCH(” “; A2)-1);0)
วิธีเดียวในการแยกชื่อกลางหลายชื่อ
จะเกิดอะไรขึ้นถ้าคนในรายชื่อของคุณมีชื่อกลางหลายชื่อ เมื่อใช้สูตรข้างต้น ระบบจะดึงเฉพาะชื่อกลางของชื่อแรกเท่านั้น
ในการแก้ปัญหานี้ คุณสามารถลองใช้วิธีอื่นในการแยกชื่อกลาง หากคุณสร้างคอลัมน์ชื่อและนามสกุล คุณสามารถตัดออกได้ง่ายๆ ที่เหลือจะนับเป็นชื่อกลาง
สูตรนี้คือ:
= TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))
ที่นี่ cell1 หมายถึงตัวชี้เซลล์ภายใต้ชื่อเต็มของคอลัมน์, cell2 หมายถึงตัวชี้เซลล์ภายใต้คอลัมน์ First Name ในขณะที่ cell3 หมายถึงตัวชี้เซลล์ภายใต้คอลัมน์ Last Name ในตัวอย่างข้างต้น เราได้รับ:
=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2))))
หรือ:
=TRIM(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2))))
ถ้าคุณใช้สูตรนี้ คุณจะไม่ต้องกังวลเรื่องค่าศูนย์
สรุปด่วน
นี่คือสูตรที่คุณสามารถใช้เพื่อแยกชื่อเต็มออกเป็นส่วน ๆ ได้:
ชื่อจริง: =LEFT(เซลล์,FIND(” “,เซลล์,1)-1)
นามสกุล: =RIGHT(เซลล์, LEN(เซลล์) – SEARCH(“#”, SUBSTITUTE(cell,” “, “#”, LEN(cell) – LEN(SUBSTITUTE(cell, ” “, “”))))
ชื่อกลาง: =IFERROR(MID(เซลล์, SEARCH(” “, เซลล์) + 1, SEARCH(” “, เซลล์, SEARCH(” “, เซลล์)+1) – SEARCH(” “, เซลล์)-1),0)
สูตรทางเลือกสำหรับชื่อกลาง: = TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))
การแยกชื่อและนามสกุลโดยไม่ต้องใช้สูตร
หากคุณไม่ต้องการพิมพ์สูตรจำนวนมากที่อาจป้อนผิด ให้ใช้ประโยชน์จากตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์ในตัวของ Excel
- ตรวจสอบให้แน่ใจว่า ข้อมูล แท็บถูกเลือกจากเมนูด้านบนและไฮไลต์คอลัมน์ที่คุณต้องการแปลง
- จากนั้นคลิกที่ ข้อความเป็นคอลัมน์.
- ต่อไปให้แน่ใจว่า ตัวคั่น ถูกเลือกและคลิก ต่อไป.
- ตอนนี้ เลือก ช่องว่าง จากตัวเลือกและคลิก ต่อไป.
- จากนั้นเปลี่ยน ปลายทาง ถึง "$B$2” และคลิก เสร็จสิ้น.ผลลัพธ์ที่ได้ควรมีลักษณะเช่นนี้
คำสุดท้าย
มีหลายวิธีในการแก้ปัญหานี้ใน Excel หากไม่มีตัวเลือกใดที่ทำได้ตามที่คุณต้องการ ให้ทำการค้นคว้าเพิ่มเติม
การใช้สูตรนั้นค่อนข้างง่ายและไม่ได้ขึ้นอยู่กับเวอร์ชันของ Excel ที่คุณใช้อยู่ แต่น่าเสียดายที่คุณยังอาจพบข้อผิดพลาด
ตัวอย่างเช่น หากชื่อเต็มของบุคคลขึ้นต้นด้วยชื่อสกุล ก็จะแยกจากกันในทางที่ผิด สูตรจะมีปัญหากับนามสกุลที่มีคำนำหน้าหรือคำต่อท้าย เช่น le Carré หรือ van Gogh หากชื่อของใครบางคนลงท้ายด้วยจูเนียร์ นั้นจะถูกระบุเป็นนามสกุลของพวกเขา
อย่างไรก็ตาม มีการแก้ไขที่คุณสามารถเพิ่มเพื่อแก้ไขปัญหาเหล่านี้ได้ตามที่ปรากฏ การทำงานกับสูตรช่วยให้คุณมีความยืดหยุ่นที่จำเป็นในการจัดการกับความซับซ้อนเหล่านี้