วิธีแยกชื่อและนามสกุลใน Excel

คุณสามารถใช้ Excel เพื่อแบ่งข้อมูลออกเป็นชิ้นเล็กๆ การค้นหาข้อมูลที่คุณต้องการและจัดการกับข้อมูลนั้นเป็นเป้าหมายสำคัญสำหรับผู้ใช้ Excel หลายคน

หากคุณมีชื่อเต็มของบุคคลหนึ่ง คุณอาจต้องศูนย์เพียงแค่ชื่อหรือนามสกุลของบุคคลนั้น ตัวอย่างเช่น หากคุณกำลังส่งอีเมลอัตโนมัติที่เป็นมิตรไปยังลูกค้าของคุณ คุณต้องใช้ชื่อของพวกเขาเพื่อหลีกเลี่ยงการฟังดูไม่มีตัวตน หากคุณกำลังดูรายชื่อผู้ตอบแบบสำรวจ อาจเป็นเรื่องสำคัญที่จะต้องใช้นามสกุลหรือซ่อนนามสกุลเพื่อรักษาความเป็นนิรนาม

Excel ทำให้กระบวนการนี้ตรงไปตรงมา และมีแนวทางต่างๆ มากมายที่คุณสามารถทำได้ นี่คือบทช่วยสอนที่จะช่วยคุณสร้างคอลัมน์ชื่อและนามสกุลแยกกันโดยใช้สูตร เราครอบคลุมปัญหาของชื่อกลางเช่นกัน

สูตร Excel สำหรับแยกชื่อออกเป็นส่วนๆ

คุณจะเริ่มต้นที่ไหน

การแยกชื่อ

นี่คือสูตรทั่วไป:

=LEFT(เซลล์,FIND(” “,เซลล์,1)-1)

ในการดำเนินการ ให้แทนที่เซลล์ด้วยตัวชี้เซลล์ที่มีชื่อเต็มเป็นชื่อแรกที่คุณต้องการแยก ในตัวอย่างนี้ คุณต้องการเลือก B2 และป้อนสูตร:

=LEFT(A2,FIND(" ",A2,1)-1)

อย่างไรก็ตาม สิ่งสำคัญคือต้องสังเกตว่าในอุปกรณ์บางรุ่น สูตรนี้ใช้เครื่องหมายอัฒภาคแทนเครื่องหมายจุลภาค ดังนั้น หากสูตรด้านบนใช้ไม่ได้ คุณอาจต้องใช้เวอร์ชันต่อไปนี้แทน:

=LEFT(เซลล์;FIND(" ";เซลล์;1)-1)

ในตัวอย่าง คุณจะใช้:

=LEFT(A2,FIND(" ",A2,1)-1)

ตอนนี้คุณสามารถลากที่จับเติมลงไปที่ส่วนท้ายของคอลัมน์ชื่อ

ฟังก์ชัน LEFT ช่วยให้คุณแยกสตริง โดยเริ่มจากด้านซ้ายสุดของข้อความ ส่วน FIND ของสูตรนี้จะค้นหาช่องว่างแรกในชื่อเต็ม ดังนั้นคุณจะได้ส่วนของชื่อเต็มที่อยู่ข้างหน้าช่องว่าง

ดังนั้น ชื่อที่มียัติภังค์จึงอยู่ด้วยกัน และชื่อที่มีอักขระพิเศษก็เช่นกัน แต่คอลัมน์ชื่อเต็มของคุณจะไม่มีชื่อกลางหรือชื่อย่อกลาง

เครื่องหมายจุลภาคหรือเครื่องหมายอัฒภาค?

ทำไมสูตรไม่เหมือนกันสำหรับทุกคน?

สำหรับผู้ใช้ Excel หลายคน ฟังก์ชัน Excel จะใช้เครื่องหมายจุลภาคเพื่อแยกข้อมูลที่ป้อนเข้า แต่ในบางอุปกรณ์ การตั้งค่าภูมิภาคจะแตกต่างกัน

เมื่อต้องการค้นหาสัญลักษณ์ที่ใช้โดย Excel ของคุณ เพียงเริ่มพิมพ์ในสูตร เมื่อคุณเริ่มป้อน=LEFT(คุณจะเห็นข้อความโฮเวอร์ที่จะแนะนำการจัดรูปแบบที่เหมาะสม

แยกนามสกุล

ใช้แนวทางเดียวกันในการแยกนามสกุล คราวนี้ คุณควรใช้สูตร RIGHT ซึ่งจะแยกสตริงที่เริ่มจากด้านขวา

สูตรที่คุณต้องการคือ:

=RIGHT(เซลล์, LEN(เซลล์) – SEARCH(“#”, SUBSTITUTE(เซลล์,” “, “#”, LEN(เซลล์) – LEN(SUBSTITUTE(เซลล์, ” “, “”)))))

ในตัวอย่างด้านบน คุณจะใช้สูตรต่อไปนี้ในเซลล์ 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(” “, เซลล์))

หรือ:

=RIGHT(A2,LEN(A2)-SEARCH(" ",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),"")

หรือ:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1),"")

หรือ:

=IFERROR(MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1);"")

วิธีหนึ่งในการแยกชื่อกลางหลาย ๆ ชื่อ

จะเกิดอะไรขึ้นหากมีคนในรายชื่อของคุณมีชื่อกลางหลายชื่อ เมื่อใช้สูตรข้างต้น เฉพาะชื่อกลางเท่านั้นที่จะถูกเรียกคืน

เพื่อแก้ปัญหานี้ คุณสามารถลองใช้วิธีการอื่นในการแยกชื่อกลาง หากคุณสร้างคอลัมน์ชื่อและนามสกุลแล้ว คุณสามารถตัดออกได้ ที่เหลือจะนับเป็นชื่อกลาง

สูตรนี้คือ:

=TRIM(MID(เซลล์1,LEN(เซลล์2)+1,LEN(เซลล์1)-LEN(เซลล์2&เซลล์3)))

ในที่นี้ เซลล์1 หมายถึงตัวชี้เซลล์ใต้คอลัมน์ชื่อเต็ม เซลล์2 หมายถึงตัวชี้เซลล์ใต้คอลัมน์ชื่อ ในขณะที่เซลล์3 หมายถึงตัวชี้เซลล์ใต้คอลัมน์นามสกุล ในตัวอย่างด้านบน เราได้รับ:

=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(เซลล์,” “, “#”, LEN(เซลล์) – LEN(SUBSTITUTE(เซลล์, ” “, “”)))) )

ชื่อกลาง: =IFERROR(MID(เซลล์, SEARCH(" ", เซลล์) + 1, SEARCH(" ", เซลล์, SEARCH(" ", เซลล์)+1) – SEARCH(" ", เซลล์)-1)," ”)

สูตรสำรองสำหรับชื่อกลาง: =TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))

การแยกชื่อและนามสกุลโดยไม่ต้องใช้สูตร

หากคุณไม่อยากพิมพ์สูตรจำนวนมากที่อาจป้อนผิด ให้ใช้ประโยชน์จากตัวช่วยแปลงข้อความเป็นคอลัมน์ในตัวของ Excel

1. ตรวจสอบให้แน่ใจว่า ได้เลือกแท็บ Dataจากเมนูด้านบนแล้วไฮไลต์คอลัมน์ที่คุณต้องการแปลง
2. จากนั้นคลิกที่  Text to Columns
3. ถัดไป ตรวจสอบให้แน่ใจว่า  ได้เลือกตัวคั่น แล้วคลิก ถัดไป
4. ตอนนี้ เลือกSpace จากตัวเลือกแล้วคลิกถัดไป
5. จากนั้น เปลี่ยน  ปลายทางเป็น “ $B$2 ” แล้วคลิก  เสร็จสิ้น ผลลัพธ์ที่ได้ควรมีลักษณะเช่นนี้

คำสุดท้าย

มีวิธีอื่นอีกมากมายในการแก้ปัญหานี้ใน Excel หากไม่มีตัวเลือกใดที่ใช้งานได้ตามที่คุณต้องการ ให้ทำการวิจัยเพิ่มเติม

การใช้สูตรนั้นค่อนข้างง่ายและไม่ได้ขึ้นอยู่กับเวอร์ชันของ Excel ที่คุณใช้ แต่น่าเสียดายที่คุณยังคงอาจพบข้อผิดพลาด

ตัวอย่างเช่น หากชื่อเต็มของบุคคลขึ้นต้นด้วยชื่อสกุล ก็จะแยกออกจากกันผิดทาง สูตรจะมีปัญหากับนามสกุลที่มีคำนำหน้าหรือคำต่อท้าย เช่น le Carré หรือ van Gogh ถ้าชื่อใครลงท้ายด้วย Jr. ชื่อนั้นจะถูกระบุเป็นนามสกุล

อย่างไรก็ตาม มีการแก้ไขที่คุณสามารถเพิ่มเพื่อแก้ไขปัญหาเหล่านี้ตามที่ปรากฏ การทำงานกับสูตรช่วยให้คุณมีความยืดหยุ่นในการรับมือกับความซับซ้อนเหล่านี้