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

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

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

วิธีแยกข้อความ 1 : Text to Column

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

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

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

วิธีแยกข้อความ 2 : ใช้สูตร

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

คำตัวต้น

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

หาตำแหน่งตัวคั่นแรก : =FIND("-",A2)

เอาข้อความข้างซ้าย : =LEFT(A2,FIND("-",A2)-1) 

ที่ต้อง -1 เพราะไม่ต้องการเครื่องหมาย – มาด้วย จึงต้องร่นไปทางซ้ายอีก 1 ตำแหน่ง

คำตัวกลาง

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

ซึ่ง – ตัวหลัง หากได้ 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)

คำตัวหลัง

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

แต่จะเอา RIGHT กี่ตัวดี?  วิธีคิดคือเอาจำนวนตัวอักษรทั้งหมด – ตำแหน่งตัวคั่นที่สอง

 =LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2)) 

จากนั้นก็ใช้ RIGHT ได้เลย

 =RIGHT(A2,   LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2))   ) 

วิธีแยกข้อความ 3 : Power Query

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

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

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

Add Column -> Duplicate Column แรกออกมาก่อน ไม่งั้นจะไม่เหลือตัว Original เก็บไว้

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

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

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

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

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

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

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

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

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

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

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

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

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

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

บทสรุป

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

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