เชี่ยวชาญ Text-to-Columns ใน Excel: 3 เทคนิคปฏิบัติจริง

Text to Columns Wizard (วิซาร์ดแปลงข้อความเป็นคอลัมน์) ของ Excel เป็นฟีเจอร์สำคัญมากที่มีหลายประโยชน์ ในบทความนี้ จะสอนวิธีแปลงข้อความเป็นคอลัมน์ใน Excel เช่น แยกคอลัมน์ของฐานข้อมูล بطريقของตัวเอง ดึงข้อมูลสำคัญจากอีเมล แม้แต่ตัวเลือกขั้นสูงของ Text to Columns Wizard สำหรับแปลงเครื่องหมายลบนำหน้า (trailing minus signs) เป็นตัวเลขลบ

คุณสามารถดาวน์โหลดสมุดงานฝึกปฏิบัติได้จากลิงก์ด้านล่าง

3 กรณีปฏิบัติจริง แปลงข้อความเป็นคอลัมน์ใน Excel

ในส่วนนี้ จะพบ 3 กรณีปฏิบัติจริงในการแปลงข้อความเป็นคอลัมน์ใน Excel จะอภิปรายพร้อมภาพประกอบทีละขั้นตอน มาดูเลยครับ

1. แยกคอลัมน์ด้วยฟีเจอร์ Text to Column

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

ฐานข้อมูลมีพลังก็ต่อเมื่อคุณทำให้มันมีพลัง คุณจะทำอย่างไร? จะหลีกเลี่ยงปัญหาที่กล่าวมาข้างต้นได้อย่างไร?

มาอธิบายตัวอย่างและวินิจฉัยปัญหาก่อน

นี่คือภาพรวมชุดข้อมูลตัวอย่าง ในชุดข้อมูลนี้ มีข้อมูลบางส่วนเกี่ยวกับบุคคล (เช่น Contact Name ชื่อผู้ติดต่อ, Address ที่อยู่, Date of Birth วันเกิด ฯลฯ)

ฐานข้อมูลสำคัญของเราพร้อมแล้ว ตอนนี้จะจัดการให้มันมีประโยชน์มากขึ้น อยากจัดเรียงชื่อตามตัวอักษร

ปัญหาที่นี่? ชื่อและนามสกุลอยู่ด้วยกันในคอลัมน์เดียวกัน

อยากรู้ว่ามีคนจาก Illinois กี่คนในรายการ ปัญหา? ที่อยู่ทั้งหมดถูกใส่ในคอลัมน์เดียว

อยากรู้ว่ามีคนอายุ 20-30 ปี กี่คน ถูกแล้ว ไม่ทำได้ เหมือนกัน คอลัมน์เดียว

เห็นรูปแบบแล้วใช่ไหม? ข้อมูลเยอะเกินไปในแต่ละคอลัมน์

หากแยกอยู่คนละคอลัมน์ จะง่ายขึ้นมาก

เพื่อแก้ปัญหาแบบนี้ สามารถใช้ 3 เครื่องมือ:

1. ใช้ฟีเจอร์ Text to Columns ใน Excel
2. ใช้ Flash Fill ฟีเจอร์ใหม่ใน Excel 2013
3. ใช้ฟังก์ชัน Excel แบ่งข้อมูลคอลัมน์เป็นชิ้นเล็กๆ สุดๆ

เพื่อรู้ว่า Flash Fill ทำงานอย่างไร อ่านบทความ: วิธีใช้ Flash Fill ใน Excel 2013

ตัวเลือกที่ 3 ก็เป็นหัวข้อย่อยครบถ้วนอยู่แล้ว

เราจะเน้นที่ฟีเจอร์ Text to Columns

1.1. แยกชื่อ (Splitting the Names)

มาทำงานกับคอลัมน์ Contact ก่อน ทำตามขั้นตอนด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกคอลัมน์ทั้งหมด สามารถเลือกได้ด้วยเมาส์ หรือวิธีอื่น ใช้แป้นพิมพ์ กด CTRL + SHIFT + Down Arrow คอลัมน์ทั้งหมดจะถูกเลือกง่ายๆ
• เปิดริบบิ้น Data และในกลุ่มคำสั่ง Data Tools คลิกที่ฟีเจอร์ Text to Columns

• ที่นี่ กล่อง Convert Text to Columns Wizard จะปรากฏ แสดง Step 1 of 3 โดยค่าเริ่มต้น ปุ่ม radio Delimited (คั่นด้วยตัวแบ่ง) ถูกเลือก คลิก Next

♦♦หมายเหตุ: สำหรับกรณีของเรา ตัวเลือก Fixed Width (ความกว้างคงที่) ไม่ใช้ได้ คุณเลือกได้เมื่อแน่ใจถึงความกว้างคอลัมน์ใหม่ และรู้ว่าข้อมูลจะถูกตัดให้พอดีแค่จากมิติ ดังนั้น Delimited จึงเหมาะกับปัญหาของเรา

• หลังจากนั้น 進入 Step 2 of 3 ที่นี่เห็น Delimiters (ตัวแบ่ง) บางตัว เรารู้ว่า Space (ช่องว่าง) เป็น Delimiter ในกรณีนี้ คุณจะเห็นตัวอย่างข้อมูล (data preview) ในหน้าต่างนี้ ลองเลื่อนลงดูว่าข้อมูลที่แยกออกมาถูกต้องหรือไม่ คลิก Next

• คุณสามารถเลือก Column data format เป็น General โดยค่าเริ่มต้นถูกเลือกแล้ว
• หากเลือกวันที่ สามารถเลือกรูปแบบวันที่ได้ โดยค่าเริ่มต้น DMY (วัน เดือน ปี) ถูกเลือกในกรณีของผม (DMY หมายถึง Day, Month, Year) แต่มีตัวเลือกอื่นใน dropdown
• เลือกช่อง Destination (ปลายทาง) ว่าข้อมูลจะวางที่ไหน (เช่น เซลล์ G4)
• ตอนนี้ คลิก Finish

• และนี่คือผลลัพธ์สุดท้าย

คุณเห็นคอลัมน์ Contact ถูกแยกเป็น 2 คอลัมน์ ผมนำชื่อคอลัมน์แรกว่า First Name (ชื่อ) และคอลัมน์ที่สองว่า Last Name (นามสกุล) การแยกข้อมูลจากคอลัมน์เดียวที่มี ชื่อ โดยใช้ฟีเจอร์ Text to Columns ไม่ได้ยากเลย

1.2. แยกที่อยู่ (Splitting Addresses)

สมมติพยายามแยกคอลัมน์ City State และ ZIP เป็นองค์ประกอบเล็กๆ: City (เมือง), State (รัฐ), และ Zip (รหัสไปรษณีย์) ดูคอลัมน์นี้ มีตัวแบ่ง 2 ประเภท: Comma (จุลภาค) และ Space (ช่องว่าง) ในกรณีนี้ ต้องแยกเป็น 2 ขั้นตอน

ทำดังนี้ครับ:

• ขั้นแรก แยกคอลัมน์นี้โดยใช้ตัวแบ่ง Comma (เปิด Wizard ตาม วิธี 1.1)

• ที่นี่ คอลัมน์ถูกแบ่ง 2 ส่วน เราตั้งชื่อคอลัมน์แรกว่า City และคอลัมน์ที่สองว่า State Zip

• ตอนนี้ จะแบ่งคอลัมน์ State Zip โดยใช้ตัวแบ่ง Space

ง่ายมากใช่ไหม!

1.3. แปลงข้อความเป็นวันที่ (Convert Text to Date)

มาลองทำงานกับคอลัมน์ Date of Birth (วันเกิด)

ส่วนแรกของคอลัมน์นี้คือ Month (เดือน) > ตามด้วย day (วัน) และ > year (ปี) ที่นี่จะใช้รูปแบบ Date

• ขั้นแรก เลือกคอลัมน์ Date of Birth และเปิด wizard ตาม วิธี 1.1

• ที่นี่ จากรูปแบบข้อมูลคอลัมน์ เลือกปุ่ม Date แล้วเลือกตัวเลือก MDY จาก drop-down > เปลี่ยนเซลล์ Destination (เช่น L4) และสุดท้าย > คลิกปุ่ม Finish

• ดังนั้น คุณจะเห็นว่าคอลัมน์ถูกแปลงเป็นคอลัมน์วันที่อย่างสวยงาม

ไปแล้ว!! คุณเห็นแล้วว่าการใช้ฟีเจอร์ Text to Columns เพื่อออกแบบใหม่ฐานข้อมูลที่ทำให้ตาหงาย ไม่ได้ยากเลย

2. ดึงโดเมนและชื่อผู้ใช้ออกจาก Email IDs ด้วย Text to Column ใน Excel

ฟีเจอร์ Text to Column ให้ตัวเลือกง่ายสำหรับแยก Email Addresses (ที่อยู่อีเมล) เป็น Username (ชื่อผู้ใช้) และ Domain (โดเมน)

เพื่อดำเนินการนี้ ดำเนินการดังด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกอีเมลที่ต้องการแยก และเปิด Convert Text to Columns Wizard ตาม วิธี 1.1

ใน Step 1 ของ Converting Text to Column Wizard เลือก Delimited และคลิก Next

• ตอนนี้ ในขั้นตอนที่ 2 ของ Converting Text to Column Wizard ให้ติ๊กที่ตัวเลือก Other (อื่นๆ) และพิมพ์ @ ตรงข้างๆ มัน กดไปที่ตัวเลือก Next

• หลังจากนั้น ใน Step 3 ตั้งค่า Destination (เช่น C2:D8) และคลิก Finish

• คุณจะเห็น Usernames (ชื่อผู้ใช้) และ Domains (โดเมน) ถูกแบ่งเป็น 2 คอลัมน์

3. Text to Columns ขั้นสูงสำหรับตัวเลขลบ (Advanced Text to Columns for Negative Numbers)

สมมติ คุณมีตัวเลขในคอลัมน์ที่มีเครื่องหมายลบนำหน้า (trailing minus sign)

เพื่อแปลงข้อความเป็นคอลัมน์ใน Excel สำหรับตัวเลขลบนี้ ให้เลือกตัวเลข และเริ่มทำฟีเจอร์ Text to Column

ขั้นตอน:

• ขั้นแรก เปิด wizard ตาม วิธี 1.1 และใน step 1 ของ Converting Text to Column Wizard เลือก Delimited และคลิก Next

• ใน Step 2 ในตัวเลือก Delimiters ไม่เลือกอะไรเลย กด Next

• ใน Step 3 กดที่ตัวเลือก Advanced (ขั้นสูง) ตอนนี้ ใน Advanced Text Import Settings ให้ติ๊กที่ Trailing minus for the negative numbers (เครื่องหมายลบนำหน้าสำหรับตัวเลขลบ) และคลิก OK

• หลังจากนั้น ตั้งค่า Destination และคลิก Finish

• สุดท้าย คุณจะเห็นผลลัพธ์ด้านล่างหลังจากนี้

สรุป

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

Happy Excel! 🙂

การอ่านเพิ่มเติมที่เกี่ยวข้อง

• Using Text to Columns to split text in Excel
• Creating Email Addresses from a Single Column with Flash Fill, TEXT Formulas & Commentator's Text Formula Suggestions
• How to split text into multiple cells in Excel
• How to Split Cells in Excel (The Ultimate Guide)
• How to split a cell in half in Excel (both diagonally & horizontally)