แยกข้อความ ใน Excel ทำได้หลายวิธี ขึ้นอยู่กับว่าลักษณะของข้อความที่ต้องการแยกนั้นมีลักษณะเป็นอย่างไร มีตั้งแต่การใช้วีธีง่าย ๆ ไปจนถึงใช้สูตรค่อนข้างยุ่งยากเล็กน้อย แล้วแต่ว่าโจทย์ที่ให้มามีรูปแบบอย่างไร
จะพยายามอธิบายวิธีแนวคิดว่า ถ้าเจอลักษณะต่าง ๆ เราจะแก้ปัญหาอย่างไร อาจจะยาวหน่อย บางคนอาจจะไม่ชอบที่อ่านอะไรยาว ๆ แต่ได้โปรดอ่านเถอะ เพราะสำหรับ Excel นี้ มันมีวิธีแก้ปัญหาได้หลายวิธี และแต่ละวิธีก็มีความเหมาะสมแตกต่างกันไป ถ้าท่านเข้าใจว่า เราทำอะไร เพื่ออะไร เพราะอะไร มันจะช่วยให้การคิดสูตรหรือวิธีการออกมาราบรื่นมาก
เนื้อหาโดยรวม :)
แยกตัวเลขจากข้อความ ด้วย Flash Fill
สำหรับ Flash Fill มีมาตั้งแต่สมัย Excel 2013 แล้ว ความมหัศจรรย์ของ Flash Fill คือ ถ้า Cell ที่เราต้องการแยกเฉพาะบางส่วน มีรูปแบบ หรือ pattern ที่ชัดเจน มันจะแยกให้เลยโดยไม่ต้องทำอะไร!
ยกตัวอย่างเช่น ชื่อเชต/อำเภอ กับ รหัสไปรษณีย์ และเราต้องการแยกออกเป็นสองคอลัมน์ แบบนี้มีรูปแบบชัดเจน วิธีการทำง่ายมาก
พิมพ์สิ่งที่เราต้องการ หลัง Cell ที่ต้องการแบ่งแยก ในที่นี้ เรามีข้อความที่ต้องการแยก อยู่ที่คอลัมน์ A ถ้าเราต้องการชื่อเขต ซึ่งเป็นตัวอักษรทั้งหมด สิ่งที่เราทำก็แค่ พิมพ์ จตุจักรที่ B1 พิมพ์ จอมทองที่ B2 จากนั้น Flash Fill จะทำที่เหลือให้โดยอัตโนมัติ โคตรง่าย
แต่ข้อจำกัด จะอยู่ที่ รูปแบบต้องเหมือนกันทั้งหมดเท่านั้น อย่างเช่น ในคอลัมน์ C เราจะแยกรหัสไปรษณีย์ เราก็ทำอย่างที่เราทำ คือพิมพ์รหัสไปรษณีย์ 10900 ในช่อง C1 และ 10150 ใสช่อง C2 Flash Fill จะเติมข้อมูลให้โดยอัตโนมัติ แต่โปรดสังเกตที่ช่อง C4 ซึ่งต้นทาง เป็น ดินแดง104-00 ซึ่งไม่ตรงตามรูปแบบที่เราวางไว้ (คือเลข 5 ตัว) มันเลยขึ้นเป็น 0
ดังนั้น ถ้าจะใช้ Flash Fill ก็ต้องมั่นใจว่ารูปแบบที่ต้องการแยกนั้น เหมือนกันหมดจริง ๆ
TIP สำหรับบางเครื่องที่ไม่แสดง Flash Fill ให้เข้าไปที่ Data > Flash Fill หรือใช้ keyboard Shortcust คือ Ctrl + E TIP สำหรับการตั้ง Flash Fill แบบอัตโนมัติ ให้เข้าไปที่ Options > Advanced > Editing Options > ติ๊กเครื่องหมายถูกตรง Automatically Flash Fill box
แยกข้อความ โดย Text to Columns
รู้จำนวนอักขระที่แน่นอน
ถ้าในข้อความนั้น มีจำนวนอักขระที่แน่นอนเท่ากันทั้งหมด เช่น เป็นรหัสอะไรสักอย่าง มีตัวอักษร 2 ตัว และตัวเลขอีกสัก 6 ตัว เป็นลักษณะนี้เหมือนกันทุกอย่าง แบบนี้เราใช้ Text To Columns แบบ การแบ่งคอลัมน์โดยใช้ความกว้างคงที่ (fixed width)
จำนวนอักขระไม่แน่นอนแต่มีตัวคั่นชัดเจน
ถ้าจำนวนอักขระไม่แน่นอนเท่ากันทั้งหมด แต่มีอะไรที่ใช้เป็นตัวคั่นได้ เช่น ช่องว่าง ขีดกลาง หรืออื่น ๆ ที่ชัดเจน แบบนี้เราใช้ Text To Columns แบบ การแบ่งคอลัมน์โดยใช้อักขระคั่น (delimiter)
สำหรับเรื่อง Text To Columns นั้นเคยเขียนถึงไปแล้ว รบกวนย้อนกลับไปอ่านรายละเอียดวิธีการหน่อยนะจ๊ะ
แยกข้อความ โดยใช้ Power Query
Power Query เป็นเครื่องมือที่ Excel ติดตั้งมาให้ตั้งแต่เวอร์ชัน 2016 ถ้าเป็นเวอร์ชันก่อนหน้านั้นต้องดาวน์โหลด add-in มาใช้
ความจริง วิธีนี้ จะคล้ายกับการใช้ Text to Columns คือเราจะใช้ตัวคั่น หรือ จำนวนอักขระที่แน่นอนในการแยกข้อความ แต่ข้อดีกว่า คือ ถ้าเรามีการปรับปรุงข้อมูล เช่นเพิ่มข้อมูล จะอัปเดตให้โดยไม่ต้องมาทำใหม่ ในขณะที่ Text to Columns สะดวกจริง แต่ถ้ามีการปรับปรุงข้อมูลต้นทาง จะต้องกลับมาทำใหม่
เริ่มต้นด้วยการ เลือก คอลัมน์ หรือ Range ที่ต้องการ แล้วไปที่ริบบอน DATA ใช้ Get DATA จาก Table/Range เมื่อกดแล้วจะมี Query Power Editor ขึ้นมา
ถ้าต้องการเก็บคอลัมน์เดิมไว้ด้วย ให้กดริบบอน Add column แล้ว Duplicate คอลัมน์ไว้ เพื่อเก็บต้นทางไว้เผื่อแก้ไขปรับปรุง แต่ ถ้าไม่ต้องการแสดงก็ไม่เป็นไร ต้นฉบับจะยังอยู่ใน sheet เดิม ที่เรากำลังทำอยู่นี้จะสร้าง sheet ใหม่
รู้จำนวนอักขระที่แน่นอน
by Number of Characters
ไปที่ Transform แล้วไปที่ Split Column เลือก by Number of Characters
จากตัวอย่าง คอลัมน์ที่เราต้องการแยกข้อความ จะมีทั้งหมด 9 อักขระ เราจะแบ่งเป็นสามคอลัมน์ คอลัมน์ละ 3 อักขระเท่ากัน ตรง number of characters ใส่ เลข 3 (คือ 3 อักขระ แล้วก็ เลือกเป็น Repeatedly ได้เลย
หรือ ในหน้าต่างที่ขึ้นมา เลือก Advance จะมีให้ใส่ว่า จะแบ่งเป็นกี่คอลัมน์ ก็ได้เหมือนกัน
เสร็จแล้วกด OK จะมีหน้าตาให้ดูว่าเป็นอย่างไร
ถ้าหากต้องการแก้ไข หรือ ยกเลิกขั้นตอนไหน โปรดดูทางขวามือ ปกติจะขึ้น Query Setting ซึ่งจะมี Applied Steps สามารถกดลบทิ้งในแต่ละขั้นตอนได้
เมื่อได้อย่างที่พอใจแล้ว ไปที่ HOME กด Close & Load มันจะปิดหน้าต่าง Editor แล้วก็สร้าง Sheet ใหม่ให้
By Position
อีกตัวเลือกหนึ่ง กำหนดไปเลยว่า จะให้แบ่งที่ตำแหน่ง (position) ใด เช่น 0,3,6 จะแบ่งเป็นสามคอลัมน์แยกที่ตำแหน่งอักขระที่ระบุไว้
อย่าลืมว่า ถ้าจะเอาตั้งแต่อักขระแรก ต้องใส่ 0 คือ เริ่มต้นด้วย เพราะถ้าใส่มาแค่ 3,6 มันจะมาแค่เฉพาะตัวอักขระลำดับที่ 4 5 6 เป็นหนึ่งคอลัมน์ และ อักขระลำดับที่ 7 8 9 เป็นอีกคอลัมน์
จำนวนอักขระไม่แน่นอนแต่มีตัวคั่นชัดเจน
ใช้ Split column เหมือนกับข้างบนที่รู้จำนวนอักขระที่แน่นอน แต่เลือก by Delimiter แทน เลือกว่าจะใช้ตัวใดเป็นตัวคั่น แบบเดียวกับ Text to Columns นั่นแหละ
เราสามารถกำหนดได้ว่า จะเอาเฉพาะ ตัวคั่นตัวแรก (ไม่ต้องแยกที่ตัวคั่นตัวอื่น) ก็ได้ หรือจะเอาทั้งหมดก็ได้ หรือจะเลือก Advance เพื่อเจาะจงลงไปว่า จะให้แยกเป็นกี่คอลัมน์ ก็ได้เช่นกัน อันนี้ถือว่าสะดวกกว่า Text to Columns พอสมควร
ทำเสร็จก็กด Close & Load ที่ Home มาดูผลลัพธ์ได้เลย
Refresh & Edit
ข้อดีของการใช้ power query ที่ดีกว่า Text to Columns ก็คือการแก้ไข ปรับปรุงข้อมูลได้ง่าย
- ไปที่ sheet ต้นฉบับ ปรับปรุงข้อมูล ให้เรียบร้อย
- แล้วไปที่ sheet ปลายทาง กด Refresh ข้อมูลการปรับปรุงก็จะมาปรากฏที่นี่ด้วย
ยอดเยี่ยมจริง ๆ
ใน Sheet ปลายทาง ที่เป็น power Query เราสามารถ แก้ไขต่าง ๆ ได้ โดยไปที่ Query แล้วเลือก Edit มันจะกลับมาที่หน้า Query Editor อีกครั้ง ดูตรง Query Setting ซึ่งจะมี Applied Steps สามารถกดลบทิ้งในแต่ละขั้นตอนได้
ซึ่งจะได้เขียนถึงการใช้งาน Power Query โดยละเอียดอีกครั้งหนึ่ง หรือ อ่านที่เว็บของไมโครซอฟต์ก่อนก็ได้
แยกข้อความ โดยเขียนสูตร
สำหรับการใช้สูตร หรือ ฟังก์ชัน ช่วยในการแยกข้อความนี้ โดยพื้นฐานทั่วไป จะต้องพิจารณาว่า สภาพแวดล้อม (หรือใช้คำเก๋ ๆ ว่า บริบท) ของข้อความที่เราจะแยกออกจากกันนั้น ใช้อะไรเป็นตัวแบ่งแยกได้บ้าง ข้อดีของการใช้สูตร คือความยืดหยุ่นเมื่อมีการปรับปรุงข้อมูล ไม่จำเป็นต้องมาเริ่มใหม่เหมือน Text to Columns
รู้จำนวนอักขระที่แน่นอน
ถ้าหาก จำนวนอักขระ มีจำนวนแน่นอน ชัดเจน เราใช้สูตร Left Right Mid เพื่อช่วยในการแยกได้
สมมติเช่น เรามีข้อมูลจำนวน 9 อักขระ เหมือนกัน ทุก Cell เราต้องการแยกเป็น 3 กลุ่ม กลุ่มละ 3 ตัวอักษร และเราไม่อยากใช้วิธี Text to Columns เพราะการใช้สูตรยืดหยุ่นกว่า บางที่ต้องนำไปอ้างอิงหรืออะไรก็ตาม เราจะใช้สูตรดังนี้
LEFT
การแยก 3 ตัวแรก จากทางซ้าย เราจะใช้ฟังก์ชัน LEFT หรือการตัดอักขระจากทางซ้าย รูปแบบของฟังก์ชัน LEFT คือ
LEFT(text,[character_num])
Text คือข้อความที่จะหา
[character_num] คือจำนวนอักขระที่ต้องการ ใส่ [ ] ให้เห็นว่า นี่คือองค์ประกอบที่ใส่ก็ได้ ไม่ใส่ก็ได้ ถ้าปล่อยว่าง ไม่กำหนดอะไรเลย จะกลายเป็นค่าว่าตัดอักขระทางซ้าย 1 อักขระ
ถ้าเราต้องการ 3 ตัวแรกจากทางซ้ายมือ สูตรก็จะเป็นแบบนี้
=LEFT(A1,3)นั่นคือ เราเอาข้อความจากเซล A1 จำนวน 3 อักขระ ก็จะได้ผลลัพธ์หน้าตาแบบนี้
MID
MID จะส่งกลับอักขระจำนวนหนึ่งจากข้อความ โดยเริ่มต้นจากตำแหน่งอักขระที่ระบุ ตามจำนวนอักขระที่ระบุ
MID(text, start_num, num_chars)
Text คือข้อความที่ต้องการค้นหา
Start_num คืออักขระที่เริ่มต้น
Num_chars คือจำนวนอักขระที่ต้องการตัด
ถ้าเราต้องการอักขระ 3 ตัวตรงกลาง จากตัวอย่าง ก็คือ เริ่มจากอักขระที่ 4 จากทางซ้ายมือ สูตรก็จะเป็นแบบนี้
=MID(A1,4,3)นั่นคือ เราเอาข้อความจากเซล A1 เริ่มจากอักขระลำดับที่ 4 จำนวน 3 อักขระ
RIGHT
RIGHT ก็จะเหมือนกับ LEFT เพียงแต่เปลี่ยนจากซ้ายกลายเป็นขวา รูปแบบของฟังก์ชัน RIGHT คือ
RIGHT(text,[character_num])
Text คือข้อความที่จะหาตัวอักขระ ในที่นี้คือ Cell A1
[character_num] คือจำนวนอักขระที่ต้องการ ถ้าไม่ใส่ จะเป็น 1 อักขระ โดยอัตโนมัติ
ถ้าเราจะแยก 3 ตัวแรก จากทางขวา สูตรก็จะเป็น
=RIGHT(A1,3)
จำนวนอักขระไม่แน่นอนแต่มีตัวคั่นชัดเจน
อีกกรณีหนึ่ง จำนวนอักขระที่จะแยกไม่แน่นอน หลากหลาย แต่มีตัวคั่นที่ชัดเจน ก็สามารถปรับแต่งฟังก์ชั่น เพื่อแยกข้อความได้เหมือนกัน โดยเราจะใช้ LEFT MID RIGHT เหมือนเดิม
เรายังใช้ฟังก์ชันคล้ายเดิม ถ้าตัดข้อความจากทางซ้าย เราจะใช้ LEFT ปัญหาคือ เราจะรู้ได้อย่างไรว่าตัวคั่นนั้นอยู่ในลำดับที่เท่าไหร่ ถ้าจะใช้ LEFT ก็ต้องรู้ว่า มีกี่อักขระ ซึ่ง ในตัวอย่างนี้ เราไม่มีจำนวนอักขระที่แน่นอน แต่เรามี – (ขีดกลาง) ที่จะบอกตำแหน่งคั่น
ทางแก้ปัญหาคือ เราจะต้องค้นหาว่า – (ขีดกลาง) นั้นอยู่ในตำแหน่งที่เท่าไหร่ เพื่อจะเอามากำหนดเป็นจำนวนอักขระที่เราต้องการจะตัด
FIND
ฟังก์ชันที่เราจะใช้เพื่อค้นหาตำแหน่งอักขระที่ต้องการคือ FIND ซึ่งมีรูปแบบการเขียนสูตรดังนี้
FIND(find_text, within_text, [start_num])
find_text คือ ข้อความที่ต้องการหา (ถ้าเป็นภาษาอังกฤษ ตัวพิมพ์เล็ก ตัวพิมพ์ใหญ่จะถือเป็นคนละตัว)
within_text คือให้หาข้อความนั้นจากไหน
Start_num คือตำแหน่งเริ่มต้น ตำแหน่งที่เท่าไหร่ เมื่อหาเจอแล้วจะส่งลำดับของตำแหน่งที่พบตัวแรก
สำหรับ [start_num] ตรงนี้ใส่ [ ] ให้เห็นว่ามันเป็น option เสริม คือ ไม่มีก็ได้ ถ้าปล่อยว่างไว้ไม่ใส่ จะถือว่า ให้เริ่มจากอักขระตัวแรกทางซ้ายมือ แต่ถ้าเราต้องการเจาะจงให้เริ่มจากตำแหน่งใดก็ได้เช่นกัน (ซึ่งจะมีผล อย่างเช่น การใช้ FIND ซ้อน FIND ที่จะอธิบายด้านล่าง)
เช่น เราต้องการหา – ในเซลล์ A1 สูตรจะเป็น
=FIND("-",A1)TIP: เราอาจจะใช้ฟังก์ชัน SEARCH แทน FIND ก็ได้
ตัดข้อความจากทางซ้าย
เราเอา FIND(“-“,A1) ไปแทนที่ใน จำนวนอักขระที่ต้องการ ของฟังก์ชัน LEFT ได้เลย แต่อย่าลืม ว่านี่คือการใช้ FIND หาตำแหน่งของ – ว่าเป็นอักขระลำดับที่เท่าไหร่ หากว่าเราเอาสูตรนี้ ไปแทนที่ในตำแหน่งจำนวนอักขระที่ต้องการในสูตร LEFT เราก็ต้อง -1 หรือ เพื่อเลื่อนจำนวนอักขระ กลับไปตัวอักขระสุดท้ายที่ต้องการตัดออกมา
=LEFT(A1,FIND("-",A1)-1)
ตัดข้อความตรงกลาง
การตัดข้อความตรงกลาง เราใช้ฟังก์ชัน MID เหมือนเดิม ซึ่งฟังก์ชันนี้ มีตัวแปรที่เราต้องใส่คือ เราจะเริ่มจากตำแหน่งใด? และ จำนวนกี่อักขระ
สำหรับการเริ่มต้น เรารู้แล้วว่าจะให้เริ่มจากตัวไหน โดยใช้ FIND เพื่อหา – (ขีดกลางตัวแรก ที่ใช้หาใน LEFT ข้างต้น) ในที่นี้ ตำแหน่งแรกที่อยากให้เริ่ม ก็คือ ตัวอักขระแรก หลังจากขีดกลางแรกที่พบ นั่นก็คือ FIND(“-“,A1) ซึ่ง เราจะต้อง +1 (เพราะให้เริ่มจากอักขระแรกหลังขีดกลางแรก จึงต้อง +1) ถ้าเป็นหาจากด้านหลัง จะขยับไปทางซ้าย ต้อง -1
ตรงนี้เราจะได้
FIND("-",A1)+1 ตั้งไว้ก่อน
สูตรจะเป็น
MID (A1, FIND("-",A1)+1, จำนวนอักขระที่ต้องการ)
จำนวนอักขระที่ต้องการนี่แหละจ้ะ เราจะหาอย่างไรดี? ที่คิดไว้คือ เราจะหาจากเอาตำแหน่งขีดกลางแรกที่พบ ลบ กับ ขีดกลางที่สองที่พบ
ใช้ FIND ซ้อน FIND
ในการหา ขีดกลางตัวที่สอง เราจะใช้ฟังก์ชัน FIND ซึ่งในไวยากรณ์สำหรับการเขียนฟังก์ชัน FIND จะมี [ตำแหน่งเริ่มต้น] (อย่างที่เขียนถึงข้างบน) ดังนั้น แทนที่เราจะให้มันหาจากอักขระตัวแรก ให้มันเริ่มหาจาก – (ขีดกลาง) ตัวแรก ซึ่ง ขีดกลางตัวแรกเราหาจากฟังก์ชัน FIND ดังนั้น มันก็เลยเป็นการใช้ FIND ซ้อน FIND
นั่นคือ
=FIND("-",A1,[ตำแหน่งแรกที่อยากให้เริ่ม])
ดังนั้น สูตรที่ออกมาจะหน้าตาแบบนี้
=FIND("-",A1,FIND("-",A1)+1)
ซึ่งเราจะต้องเอา ตำแหน่งของขีดกลางที่ 2 ไปลบกับตำแหน่งขีดกลางแรก จึงจะได้จำนวนอักขระที่ต้องการ นั่นคือ
=FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)
เมื่อนำไปแทนที่ในฟังก์ชัน MID ก็จะได้สูตรยาวเหยียดดังนี้
=MID(A1, FIND ("-", A1)+1, FIND ("-", A1, FIND ("-", A1)+1) – FIND ("-", A1)-1)
ใช้ SUBSTITUTE
สำหรับอีกวิธี แนวคิดคือ การเปลี่ยน – (ขีดกลาง) ตัวที่สอง ไปเป็นสัญลักษณ์อื่น เช่น _ (ขีดล่าง) เพื่อจะใช้ Find ได้ง่ายขึ้น (หรือเปล่า?) ฟังก์ชันที่ใช้ในการแทนที่คำหรืออักขระที่ต้องการคือ Substitute โดยสูตรหรือไวยากรณ์การเขียนจะเป็นดังนี้
SUBSTITUTE(ข้อความหรือเซลล์อ้างอิง, คำที่ต้องการแทนที่, คำที่ใช้เทนที่, [ระบุตำแหน่ง])
[ระบุตำแหน่ง] เป็น option เสริม ในกรณีที่ไม่ต้องการแทนที่ทุกตัว แต่ต้องการเปลี่ยนเฉพาะตำแหน่งใดเฉพาะเจาะจง
อย่างเช่น เราจะเปลี่ยน ขีดกลาง ตัวที่ 2 ให้เป็น ขีดล่าง สูตรก็จะเป็น
=SUBSTITUTE(A1,"-","_",2)
จากนั้นเราก็มาใช้ FIND เพื่อหา ขีดล่าง ก็คือ
=FIND("_",SUBSTITUTE(A2,"-","_",2))
เอามาใส่ในฟังก์ชัน MID เหมือนเดิม จะได้สูตรยาว ๆ แบบนี้
=MID(A1, FIND("-",A1) +1, FIND("_",SUBSTITUTE(A1,"-","_",2)) - FIND("-",A1) -1)
Tips: ในตัวอย่าง เราจะแยกเป็น 3 กลุ่ม และมี ขีดกลาง คั่นเพียง 2 ตำแหน่ง แต่ถ้าเป็นในกรณีที่เราไม่รู้ว่า มันมีขีดกลางกี่ตัวกันแน่ เราต้องใช้ LEN หรือการนับจำนวนช่วย นั่นคือ
=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))
ตัดข้อความด้านขวา
พอได้วิธีหาตำแหน่งขีดกลางตัวที่สองแล้ว การหาข้อความด้านขวาก็ไม่ยาก คือหาจากตำแหน่งของ ขีดกลาง ตัวที่สองเป็นต้นไป คำถามคือ แล้วจะรู้ได้อย่างไรว่า มันมีจำนวนอักขระเท่าไหร่? รู้จุดเริ่มละ แต่จะเอากี่ตัวดีนะ?
ง่าย ๆ เอาจำนวนอักขระทั้งหมดตั้ง ลบด้วยจำนวนอักขระถึงขีดกลางตัวที่สอง
=LEN(A1)-FIND("_",SUBSTITUTE(Aa,"-","_",2))
เอาไปใส่ใน RIGHT
=RIGHT(A1, LEN(A1)-FIND("_",SUBSTITUTE(A1,"-","_",2)) )คำแนะนำ
ทั้ง 4 วิธี มีข้อดีข้อด้อยต่างกัน ก็คงต้องเลือกเอาว่าเราต้องการใช้งานประเภทไหน เช่น
- ข้อมูลมีมาครั้งเดียว จบ ใช้ Text to Columns ก็สะดวก
- ข้อมูลที่ได้มาครั้งเดียว แต่ อาจมีปรับปรุงข้อมูลใหม่ แต่ใช้วิธีการเดิม ใช้ power query ก็เหมาะสม
- อาจจะมีการเปลี่ยนแปลงลักษณะวิธีแบ่งแยกข้อความ เช่นเปลี่ยนตำแหน่งตัดใหม่ จะใช้สูตรก็โอเคนะจ๊ะ
- ถ้ารูปแบบที่ชัดเจน แน่ใจว่าไม่มีอะไรนอกแถว การเลือก Flash Fill ก็สะดวกรวดเร็ว