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

เช่น ตัวอย่างข้างล่างนี้ มีคั่นด้วย – (แต่ว่าตำแหน่งของมันไม่แน่ไม่นอน) เราจะแยกข้อความออกมาเป็น 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 Custom Column ขึ้นมาโดย Dup ค่าจาก Field แรกไว้

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

เลือก 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 ก็ได้ผลลัพธ์เลย

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