วิธีแยกเซลล์ (ออกเป็นหลายคอลัมน์) ใน Excel

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

ตัวอย่างง่ายๆ ที่คุณต้องการแยกเซลล์ใน Excel คือเมื่อคุณมีชื่อเต็มและต้องการแยกออกเป็นชื่อและนามสกุล

หรือคุณได้รับที่อยู่' และคุณต้องการแยกที่อยู่เพื่อให้คุณสามารถวิเคราะห์เมืองหรือรหัสผ่านแยกกัน

วิธีแยกเซลล์ใน Excel

ในบทช่วยสอนนี้ คุณจะได้เรียนรู้วิธีแยกเซลล์ใน Excel โดยใช้เทคนิคต่อไปนี้:

• ใช้คุณสมบัติข้อความเป็นคอลัมน์
• ใช้ฟังก์ชันข้อความของ Excel
• ใช้ Flash Fill (ใช้ได้ในปี 2013 และ 2016)

เราเริ่มต้นกันเลย!

แบ่งเซลล์ใน Excel ด้วยข้อความเป็นคอลัมน์

ด้านล่างนี้ ฉันได้ระบุชื่อตัวละครที่ฉันชื่นชอบบางตัวที่ฉันอยากจะแยกออกเป็นเซลล์ต่างๆ :

ต่อไปนี้เป็นขั้นตอนในการแบ่งชื่อเหล่านี้เป็นชื่อและนามสกุล:

• เลือกเซลล์ที่มีข้อความที่คุณต้องการแยก (ในกรณีนี้คือ A2:A7)
• คลิกแท็บข้อมูล
• ในกลุ่มเครื่องมือข้อมูล คลิกข้อความเป็นคอลัมน์
• ในตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์:

การดำเนินการนี้จะแบ่งข้อความของเซลล์ออกเป็นสองคอลัมน์ที่แตกต่างกันในทันที

หมายเหตุ:

• ฟีเจอร์ Text to Columns จะแบ่งเนื้อหาของเซลล์ตามตัวคั่นวิธีนี้ใช้ได้ผลดีถ้าคุณต้องการแยกชื่อและนามสกุล ในกรณีของชื่อ กลาง และนามสกุล จะแบ่งออกเป็นสามส่วน
• ผลลัพธ์ที่ได้จากการใช้ฟังก์ชัน Text to Column เป็นแบบคงที่ซึ่งหมายความว่าหากมีการเปลี่ยนแปลงใดๆ ในข้อมูลเดิม คุณจะต้องดำเนินการซ้ำเพื่อรับผลลัพธ์ที่อัปเดต

แยกเซลล์ใน Excel โดยใช้ฟังก์ชันข้อความ

ฟังก์ชันข้อความของ Excel มีประโยชน์เมื่อคุณต้องการแบ่งและแยกสตริงข้อความ

แม้ว่าฟังก์ชัน Text to Column จะแสดงผลแบบคงที่ ผลลัพธ์ที่ได้จากการใช้ฟังก์ชันจะเป็นไดนามิกและอัปเดตโดยอัตโนมัติเมื่อคุณเปลี่ยนข้อมูลเดิม

แยกชื่อกับนามสกุล

สมมติว่าคุณมีข้อมูลเหมือนกันดังนี้:

ชื่อสารสกัด

ในการรับชื่อจากรายการนี้ ใช้สูตรต่อไปนี้:

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

สูตรนี้จะค้นหาอักขระเว้นวรรคตัวแรกแล้วส่งคืนข้อความทั้งหมดก่อนอักขระเว้นวรรคนั้น:

สูตรนี้ใช้ฟังก์ชัน SEARCH เพื่อรับตำแหน่งของอักขระเว้นวรรคในตัวอย่างของ Bruce Wayne ตัวละครอวกาศมาอยู่อันดับที่ 6จากนั้นจะใช้ฟังก์ชัน LEFT เพื่อแยกอักขระทั้งหมดทางด้านซ้าย

แยกนามสกุล

ในทำนองเดียวกัน ในการรับนามสกุล ให้ใช้สูตรต่อไปนี้:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

สูตรนี้ใช้ฟังก์ชัน SEARCH เพื่อค้นหาตำแหน่งของแป้นเว้นวรรคโดยใช้ฟังก์ชัน SEARCHจากนั้นจะลบตัวเลขนี้ออกจากความยาวทั้งหมดของชื่อ (กำหนดโดยฟังก์ชัน LEN)ซึ่งจะทำให้จำนวนอักขระในนามสกุล

จากนั้นใช้ฟังก์ชัน RIGHT เพื่อแยกนามสกุลนี้

หมายเหตุ:ฟังก์ชันเหล่านี้อาจทำงานไม่ถูกต้องหากมีช่องว่างนำหน้า ต่อท้าย หรือสองครั้งในชื่อคลิกที่นี่เพื่อเรียนรู้วิธีลบช่องว่างนำหน้า/ต่อท้าย/สองเท่าใน Excel

แยกชื่อด้วยชื่อ กลาง และนามสกุล

ในบางกรณี คุณอาจลงเอยด้วยชื่อผสมกัน ซึ่งบางชื่อก็มีชื่อกลางด้วย

สูตรในกรณีนี้ค่อนข้างซับซ้อน

ชื่อสารสกัด

เพื่อให้ได้ชื่อ:

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

นี่เป็นสูตรเดียวกับที่เราใช้โดยไม่มีชื่อกลางมันแค่มองหาอักขระช่องว่างตัวแรกและส่งคืนอักขระทั้งหมดก่อนเว้นวรรค

แยกชื่อกลาง

เพื่อให้ได้ชื่อกลาง:

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

ฟังก์ชัน MID เริ่มต้นด้วยอักขระช่องว่างตัวแรก และใช้ความแตกต่างในตำแหน่งของอักขระช่องว่างที่หนึ่งและที่สองเพื่อแยกชื่อกลาง

หากไม่มีชื่อกลาง ฟังก์ชัน MID จะส่งกลับข้อผิดพลาดเพื่อหลีกเลี่ยงข้อผิดพลาด จึงรวมไว้ในฟังก์ชัน IFERROR

แยกนามสกุล

ในการรับนามสกุลให้ใช้สูตรต่อไปนี้:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))

สูตรนี้จะตรวจสอบชื่อกลาง (โดยการนับจำนวนอักขระเว้นวรรค)หากมีอักขระเว้นวรรคเพียง 1 ตัว ระบบจะส่งคืนข้อความทั้งหมดทางด้านขวาของอักขระเว้นวรรค

แต่ถ้ามี 2 จะพบอักขระช่องว่างที่สองและส่งคืนจำนวนอักขระหลังช่องว่างที่สอง

หมายเหตุ: สูตรเหล่านี้ทำงานได้ดีเมื่อคุณมีเฉพาะชื่อและนามสกุล หรือชื่อ กลาง และนามสกุลอย่างไรก็ตาม หากคุณมีคำต่อท้ายหรือคำนับรวมกัน คุณจะต้องแก้ไขสูตรเพิ่มเติม

แบ่งเซลล์ใน Excel ด้วย QuickFill

Flash Fill เป็นคุณลักษณะใหม่ที่นำมาใช้ใน Excel 2013

จะมีประโยชน์มากเมื่อคุณมีแพทเทิร์นและต้องการแยกส่วนต่างๆ ของแพทเทริ์นออกอย่างรวดเร็ว

ตัวอย่างเช่น ขอข้อมูลชื่อและนามสกุล:

Quick Fill ทำงานโดยการจดจำรูปแบบและคัดลอกไปยังเซลล์อื่นๆ ทั้งหมด

ต่อไปนี้คือวิธีใช้ Flash Fill เพื่อดึงชื่อออกจากรายการ:

Flash Fill ทำงานอย่างไร

Flash Fill ค้นหารูปแบบในชุดข้อมูลและคัดลอก

Flash Fill เป็นคุณลักษณะที่ชาญฉลาดอย่างน่าประหลาดใจซึ่งทำงานได้ดีในกรณีส่วนใหญ่แต่ก็ยังล้มเหลวในบางกรณี

ตัวอย่างเช่น หากฉันมีรายชื่อที่มีชื่อผสมกัน บางคนมีชื่อกลางและบางชื่อไม่มี

หากฉันแยกชื่อกลางในกรณีนี้ Flash Fill จะส่งคืนนามสกุลอย่างไม่ถูกต้องในกรณีที่ไม่มีชื่อ

จริงๆ แล้ว นี่ยังคงเป็นการประมาณที่ดีของแนวโน้มอย่างไรก็ตาม นี่ไม่ใช่สิ่งที่ฉันต้องการ

แต่ก็ยังเป็นเครื่องมือที่ดีพอที่จะเก็บไว้ในคลังแสงของคุณ และใช้เมื่อจำเป็น

นี่เป็นอีกตัวอย่างหนึ่งของการเติมข้อความด่วนที่ได้ผลดี

ฉันมีชุดที่อยู่ซึ่งฉันต้องการดึงข้อมูลเมืองอย่างรวดเร็ว

เพื่อให้ได้เมืองอย่างรวดเร็ว ให้ป้อนชื่อเมืองของที่อยู่แรก (ในตัวอย่างนี้คือลอนดอนในเซลล์ B2) และใช้การเติมข้อความอัตโนมัติเพื่อกรอกข้อมูลในเซลล์ทั้งหมดตอนนี้ใช้ Flash Fill แล้วระบบจะให้ชื่อเมืองสำหรับแต่ละที่อยู่แก่คุณในทันที

ในทำนองเดียวกัน คุณสามารถแยกที่อยู่และแยกส่วนใดก็ได้ของที่อยู่

โปรดทราบว่าสิ่งนี้ต้องการให้ที่อยู่เป็นชุดข้อมูลที่เป็นเนื้อเดียวกันโดยมีตัวคั่นเดียวกัน (ในกรณีนี้คือจุลภาค)

หากคุณพยายามใช้ Flash Fill โดยไม่มีรูปแบบ จะแสดงข้อผิดพลาดดังนี้:

ในบทช่วยสอนนี้ ฉันพูดถึงสามวิธีในการแบ่งเซลล์ใน Excel ออกเป็นหลายคอลัมน์ (โดยใช้ Text to Column, Formulas และ Quick Fill)

ฉันหวังว่าคุณจะพบว่าบทช่วยสอน Excel นี้มีประโยชน์