เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Power QueryExcel ทั่วไปHighlights : บทความแนะนำ

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

สารพัดวิธีแยกข้อความออกจากกัน 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 Column -> Duplicate Column แรกออกมาก่อน ไม่งั้นจะไม่เหลือตัว Original เก็บไว้

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

บทสรุป

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

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

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 19
  •  
  •  
  •  
  •  
  • 19
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply