วิธีแยกชื่อและนามสกุลใน 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(เซลล์, , #, LEN(เซลล์) – LEN(SUBSTITUTE(เซลล์, , )))))

ในตัวอย่างข้างต้น คุณจะใช้สูตรต่อไปนี้ในเซลล์ C2:

= ขวา (A2, LEN (A2) - ค้นหา (#, แทนที่ (A2, #, LEN (A2) - LEN (แทนที่ (A2,))))

อีกครั้งคุณอาจต้องเปลี่ยนจากเครื่องหมายจุลภาคเป็นอัฒภาคซึ่งหมายความว่าคุณอาจต้องใช้:

= ขวา (A2; LEN (A2) - ค้นหา (#; แทนที่ (A2 ;; #; LEN (A2) - LEN (แทนที่ (A2 ;;))))

ยัติภังค์นามสกุลและนามสกุลที่มีอักขระพิเศษยังคงไม่บุบสลาย

เหตุใดสูตรนี้จึงซับซ้อนกว่าสูตรสำหรับชื่อแรก การแยกชื่อกลางและชื่อกลางออกจากนามสกุลยากกว่า

หากคุณต้องการให้ชื่อกลางและชื่อย่อแสดงด้วยนามสกุลคุณสามารถใช้สูตร:

= ขวา (เซลล์ LEN (เซลล์) - ค้นหา (เซลล์))

หรือ:

= ขวา (A2, LEN (A2) - ค้นหา (, A2))

หรือ:

= ขวา (A2; LEN (A2) - ค้นหา (; A2))

แต่ถ้าคุณต้องการแยกชื่อกลางล่ะ? สิ่งนี้พบได้น้อย แต่ก็มีประโยชน์ที่จะทราบ

การแยกชื่อกลาง

สูตรสำหรับชื่อกลางมีดังต่อไปนี้:

วิธีเล่นเกม xbox one บนพีซีโดยไม่ต้องสตรีม

= MID (เซลล์ค้นหา (เซลล์) + 1 ค้นหา (เซลล์ค้นหา (เซลล์) +1) - ค้นหา (เซลล์) -1)

ในตัวอย่างข้างต้น คุณจะได้รับ:

= กลาง (A2, ค้นหา (, A2) + 1, ค้นหา (, A2, ค้นหา (, A2) +1) - ค้นหา (, A2) -1)

ถ้า Excel ของคุณใช้อัฒภาคสูตรคือ:

= กลาง (A2; ค้นหา (; A2) + 1; ค้นหา (; A2; ค้นหา (; A2) +1) - ค้นหา (; 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 ในตัวอย่างข้างต้น เราได้รับ:

= ตัดแต่ง (MID (A2, LEN (B2) + 1, LEN (A2) -LEN (B2 & D2))))

หรือ:

= TRIM (กลาง (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),0)

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

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

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

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

คำสุดท้าย

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

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

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

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