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

คุณสามารถใช้ 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

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

คำสุดท้าย

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

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

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

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


$config[zx-auto] not found$config[zx-overlay] not found