สารพัดวิธีแยกร่างข้อความ

สารพัดวิธีแยกร่างข้อความ 1

สมมติว่าเรามีข้อความบางอย่าง แล้วเราอยากจะแยกส่วนข้อความนั้น

เช่น ตัวอย่างข้างล่างนี้ มีคั่นด้วย – (แต่ว่าตำแหน่งของมันไม่แน่ไม่นอน) เราจะแยกข้อความออกมาเป็น 3 ส่วนได้ยังไง? มาดูกัน

สารพัดวิธีแยกร่างข้อความ 2

วิธีที่ 1 : Text to Column

วิธีที่ง่ายที่สุดคือการใช้เครื่องมือ Text to Column โดยให้ Copy ข้อมูลต้นฉบับออกมาก่อน

แล้วเลือกข้อมูล แล้วไปที่ [Data]–> Data Tools –> Text to Column –> Delimited และให้ใส่เครื่องหมาย – (ที่เป็นตัวคั่น) ลงไปใน Other

สารพัดวิธีแยกร่างข้อความ 3

จากนั้นกด Finish แค่นี้ก็เสร็จแล้ว ได้เป็น 3 คอลัมน์อย่างที่ต้องการ

สารพัดวิธีแยกร่างข้อความ 4

วิธีที่ 2 : ใช้สูตร

การใส่สูตรมีข้อดีอย่างมาก คือ ข้อมูลอัปเดทโดยอัตโนมัติ โดยไม่ต้องกดเครื่องมือใดๆ เลย แต่ข้อเสียคือ เขียนยาก ถ้ายังใช้สูตรไม่คล่อง

คำตัวต้น

ตัวต้นเป็นตัวที่หาง่ายสุด ใน 3 ตัว เพราะการใช้ฟังก์ชัน FIND หรือ SEARCH ซึ่งสามารถหาตำแหน่ง – ตัวแรกได้อยู่แล้ว

  • หาตำแหน่งตัวคั่นแรก : =FIND(“-“,A2)
  • เอาข้อความข้างซ้าย : =LEFT(A2,FIND(“-“,A2)-1)
    • ที่ต้อง -1 เพราะไม่ต้องการเครื่องหมาย – มาด้วย จึงต้องร่นไปทางซ้ายอีก 1 ตำแหน่ง

สารพัดวิธีแยกร่างข้อความ 5

คำตัวกลาง

ตัวกลาง จะหาได้เราต้องรู้ตำแหน่งของ – ตัวหน้า และ – ตัวหลัง ซึ่งตัวหน้าเรารู้อยู่แล้ว

ซึ่ง – ตัวหลัง หากได้ 2 วิธี

วิธีแรก

  • ใช้ FIND ซ้ำไปอีก โดยระบุการค้นหาให้เริ่มจากตำแหน่ง – ตัวแรก โดย+เพิ่มไปอีก 1 ตำแหน่ง ซึ่งจะทำให้เจอ – ตัวที่สอง
  • =FIND(“-“,A2,FIND(“-“,A2)+1)

วิธีสอง 

  • ให้ SUBSTITUTE – ตัวที่ 2 ด้วยเครื่องหมายพิเศษอื่น เช่น |
    =SUBSTITUTE(A2,”-“,”|”,2)
  • Tips : ถ้าไม่รู้ว่าข้อความมี – กี่ตัว?
    ให้ลอง SUBSTITUTE ค่า “-” ด้วย “” แล้วลองนับจำนวนตัวอักษรดูว่าหายไปกี่ตัว
    =LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””))
  • จากนั้นให้ FIND ตำแหน่ง | อีกที จะได้
    =FIND(“|”,SUBSTITUTE(A2,”-“,”|”,2))

พอรู้ตำแหน่งของตัวที่ 2 ก็จะตัดตัวกลางและตัวท้ายได้ง่ายแล้ว

คำตัวกลาง

  • =MID(xxx,  ตัวคั่นแรก  +1,    ตัวคั่นสอง   –   ตัวคั่นแรก   -1)
  • =MID(A2,   FIND(“-“,A2)  +1,    FIND(“|”,SUBSTITUTE(A2,”-“,”|”,2))   –   FIND(“-“,A2)   -1)

สารพัดวิธีแยกร่างข้อความ 6

คำตัวหลัง

หาไม่ยากโดยใช้ RIGHT

  • แต่จะเอา RIGHT กี่ตัวดี?  วิธีคิดคือเอาจำนวนตัวอักษรทั้งหมด – ตำแหน่งตัวคั่นที่สอง
    =LEN(A2)-FIND(“|”,SUBSTITUTE(A2,”-“,”|”,2))
  • จากนั้นก็ใช้ RIGHT ได้เลย =RIGHT(A2,   LEN(A2)-FIND(“|”,SUBSTITUTE(A2,”-“,”|”,2))   )

สารพัดวิธีแยกร่างข้อความ 7

วิธีที่ 3 Power Query

ถ้า Excel 2016 จะมีมาให้เลย แต่ถ้าต่ำกว่านั้นต้องไป Download Add-in ก่อน (ฟรี) แต่โหลดให้ถูก version นะ ไม่งั้นจะลงไม่ได้

ถ้าพร้อมแล้วก็สร้าง Table ก่อน โดยเลือกข้อมูลแล้วกด Ctrl+T

สารพัดวิธีแยกร่างข้อความ 8

จากนั้นไปที่ Data (หรือ Power Query)-> From Table

สารพัดวิธีแยกร่างข้อความ 9

Add Custom Column ขึ้นมาโดย Dup ค่าจาก Field แรกไว้

ไปที่ Add Column -> Custom Column -> ดับเบิลคลิ๊ก Available Column แล้ว ok

สารพัดวิธีแยกร่างข้อความ 10

เลือก column ใหม่แล้วไปที่ Transform -> Split Column -> Delimiter

สารพัดวิธีแยกร่างข้อความ 11

เลือก custom ใส่ – ลงไป แล้ว ok

สารพัดวิธีแยกร่างข้อความ 12

ได้ผลลัพธ์ใน Power Query ดังใจแล้ว กด Close & Load ได้เลย

สารพัดวิธีแยกร่างข้อความ 13

ผลลัพธ์จะกลับมาที่ Excel ใน Sheet ใหม่

สารพัดวิธีแยกร่างข้อความ 14

จะเห็นว่าคล้ายวิธีแรก แต่ข้อดีกว่ามากๆ เลย คือ หากมีข้อมูลเพิ่มมา แค่กด Refresh ผลลัพธ์ก็จะเปลี่ยนและอัปเดทให้อัตโนมัติ!!

เพิ่ม Data ไปอีก 2 บรรทัด

สารพัดวิธีแยกร่างข้อความ 15

ไปที่ผลลัพธ์ กดคลิ๊กขวา Refresh

สารพัดวิธีแยกร่างข้อความ 16

ปรากฎว่า Data ใหม่มา แต่มีอันนึงไม่ยอมแยกข้อความให้

สารพัดวิธีแยกร่างข้อความ 17

เราสามารถไปตรวจสอบได้โดยไปที่ Query –> Edit

สารพัดวิธีแยกร่างข้อความ 18

ปรากฎว่า มีค่า Error ขึ้นมาบรรทัดนึงนี่เอง

สารพัดวิธีแยกร่างข้อความ 19

พอลองไล่คลิ๊ก Applied Steps ดูก็พบว่า สามารถทำมาถึง Split Column by Delimiter ได้โดยไม่ Error เลย

สารพัดวิธีแยกร่างข้อความ 20

ซึ่งแปลว่า Error น่าจะเกิดจากการทำ Changed Type Step สุดท้าย (เปลี่ยนตัวหนังสือเป็นตัวเลข)

เราจึงกด x ข้างหน้่า Changed Type อันสุดท้ายเพื่อบอกว่า Power Query ไม่ต้องทำ Step นี้แล้วนะ ลบ Step ทิ้งไปเลย

สารพัดวิธีแยกร่างข้อความ 21

จากนั้นกด Close & Load เป็นอันจบ เสร็จแบบเนียนๆ เลย

สารพัดวิธีแยกร่างข้อความ 22

บทสรุป

  • ถ้าทำที่เดียวจบ ใช้ Text to Column เนี่ยเจ๋งสุดแล้ว เร็วและง่าย แต่ถ้ามีข้อมูลมาใหม่ต้องทำใหม่
  • วิธีเขียนสูตรจะเขียนยากหน่อย แต่ Flexible มาก จะเขียนอะไรก็ได้ อีกอย่าง ถ้ามีข้อมูลใหม่แค่ลากสูตรก็ได้ผลลัพธ์เลย
  • วิธี Power Query ใช้ไม่ยาก แต่อาจยังไม่คุ้นเคย หัดใช้บ่อยๆ จะพบว่าทรงพลังมากๆ ข้อมูลใหม่มาแค่กด Refresh ก็ได้ผลลัพธ์เลย

ยาวหน่อยแต่หวังว่าจะถูกใจผู้อ่านนะครับ ใครสงสัยอะไรก็สามารถถามมาได้เลยครับ

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ
==========================================

  •  
  •  
  •  
  •  
  •  

Posted on: January 17, 2017
Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *