คุณสามารถใช้ 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
- ตรวจสอบให้แน่ใจว่า ข้อมูล แท็บถูกเลือกจากเมนูด้านบนและไฮไลต์คอลัมน์ที่คุณต้องการแปลง
- จากนั้นคลิกที่ ข้อความเป็นคอลัมน์ .
- ต่อไปให้แน่ใจว่า ตัวคั่น ถูกเลือกและคลิก ต่อไป
. - ตอนนี้ เลือก พื้นที่ จากตัวเลือกและคลิก ต่อไป .
- จากนั้นเปลี่ยน change ปลายทาง ถึง$ B $ 2และคลิก เสร็จสิ้น.
ผลลัพธ์ที่ได้ควรมีลักษณะเช่นนี้
.
ผลลัพธ์ที่ได้ควรมีลักษณะเช่นนี้
คำสุดท้าย
มีวิธีอื่นอีกมากมายในการแก้ปัญหานี้ใน Excel หากไม่มีตัวเลือกใดที่ทำได้ตามที่คุณต้องการ ให้ทำการค้นคว้าเพิ่มเติม
การใช้สูตรนั้นค่อนข้างง่ายและไม่ได้ขึ้นอยู่กับเวอร์ชันของ Excel ที่คุณใช้ แต่น่าเสียดายที่คุณยังอาจพบข้อผิดพลาด
ตัวอย่างเช่นหากชื่อนามสกุลของบุคคลใดขึ้นต้นด้วยชื่อสกุลก็จะแยกออกจากกันอย่างไม่ถูกต้อง สูตรจะมีปัญหากับนามสกุลที่มีคำนำหน้าหรือคำต่อท้ายเช่น le Carréหรือ van Gogh หากชื่อใครลงท้ายด้วยจูเนียร์ชื่อนั้นจะแสดงเป็นนามสกุล
อย่างไรก็ตาม มีการแก้ไขที่คุณสามารถเพิ่มเพื่อแก้ไขปัญหาเหล่านี้ตามที่ปรากฏ การทำงานกับสูตรช่วยให้คุณมีความยืดหยุ่นที่จำเป็นในการจัดการกับความซับซ้อนเหล่านี้
