สารพัดวิธีกำจัดอักขระที่ไม่ต้องการออกจากข้อความ (Clean Bad Characters)

ผมได้รับคำถามมาจากแฟนเพจหลายคนเกี่ยวกับวิธีการ Clean ข้อความขยะ หรือกำจัดเอาอักขระที่ไม่ต้องการออกจากข้อความ ซึ่งบางครั้งมันก็ง่ายมาก (แค่หลายคนยังไม่รู้ ) แต่บางทีมันก็ยากเอาเรื่องเลยล่ะ วันนี้ผมก็เลยถือโอกาสเอามาเขียนเป้นบทความดีกว่าว่าเรามีวิธีการจัดการยังไงได้บ้าง?

ผมมีข้อมูลเป็นชื่อนามสกุลดังนี้ ปรากฏว่ามันไม่สามารถ lookup เทียบกับอีกค่าที่ผมเตรียมไว้ได้ พอเอามาเช็คก็พบว่าค่ามันไม่เท่ากันจริงๆ

บางช่องก็เห็นชัดว่ามันไม่เหมือนกัน เช่น มีช่องว่างติดมา แต่บางช่องดูแล้วก็ไม่น่าติดอะไร ทำไมมันถึงไม่เท่ากัน?

แนวทางการทำความสะอาดข้อมูล ผมแนะนำให้เริ่มจากวิธีที่ง่ายที่สุด 2 อย่างก่อน นั่นคือ CLEAN และ TRIM

ตัดตัวประหลาดและการขึ้นบรรทัดใหม่ด้วย CLEAN

ฟังก์ชันนี้มีหน้าที่ในการตัดอักระที่มองไม่เห็น 32 ตัวแรกของรหัส ASCII ออกไป (ซึ่งคือ Code 1-31) (รวมถึงการขึ้นบรรทัดใหม่ซึ่งคือ Code ตัวที่ 10 ด้วย) ถ้าอยากรู้ว่ารหัสแต่ละตัวคืออะไร สามารถใส่เลข Running 1-255 แล้วใช้ฟังก์ชัน CHAR สร้างอักขระดูได้ดังนี้

ดังนั้นถ้าเราใช้ CLEAN กับข้อมูลของเราที่มีการขึ้นบรรทัดใหม่ด้วย CHAR(10) มันก็จะหายไป

ตัดช่องว่างส่วนเกินออกด้วย TRIM

TRIM เป็นฟังก์ชันที่ผมคิดว่าเจ๋งมากระดับที่สามารถช่วยให้ชีวิตของหลายๆ คนดีขึ้นหลายเท่าเลยล่ะ เพราะมันเกิดมาเพื่อจัดการปัญหาสุด Classic นั่นก็คือ มีการเว้นวรรคเกินมาข้างหน้าบ้างข้างหลังบ้าง รวมถึงเว้นระหว่างคำที่ดันเว้นไม่เป็นมาตรฐาน เช่น เคาะเท่ากันบ้างไม่เท่ากันบ้าง (หลายคนมักจะไม่ชอบการเคาะครั้งเดียวเพราะคิดว่าไม่สวย เลยหวังดีเคาะไป 2-3 รอบ ก็ทำให้เกิดปัญหาได้มากเหมือนกัน)

หลายคนมักจะไม่ชอบการเคาะครั้งเดียวเพราะคิดว่าไม่สวย เลยหวังดีเคาะไป 2-3 รอบ ก็ทำให้เกิดปัญหาได้มากเหมือนกัน

แต่ปัญหาเหล่านี้จะหมดไปหากใช้ TRIM ใน Excel เพราะมันจะตัดช่องว่างที่อยู่ข้างหน้าทั้งหมด ข้างหลังทั้งหมด แต่จะตัดช่องว่างตรงกลางที่เกิน 1 เคาะให้เหลือแค่เคาะเดียว

สรุปแล้วหลังจากใช้ TRIM แล้วจะเหลือแบบนี้

เอาอักขระออกแบบเจาะจงด้วย SUBSTITUTE

จะเห็นว่า Clean กับ TRIM สามารถจัดการปัญหาไปได้ในหลายๆ เคสแล้ว แต่ก็ยังมีบางกรณีที่เราเอาไม่ออก เช่น สมมติว่ามี . เกินมา และสมมติว่าเราต้องการเอา . ออกไปจากคำของเรา (ไม่ว่ามันจะอยู่ตรงไหนก็ถาม)

เราสามารถใช้ SUBSTITUTE ซึ่งความสามารถจริงๆ คือการแทนที่ คำเดิม ด้วย คำใหม่อีกคำนึง ได้ แต่คราวนี้เราจะเอาคำเดิมออก (“.”) แล้วไม่ใส่อะไรเข้าไปแทนเลย (ใส่แทนด้วย “”) เช่น

ตรวจสอบจำนวนตัวอักษรด้วย LEN

ทีนี้ก็เหลือตัวประหลาดตัวสุดท้ายที่เรายังจัดการไม่ได้ด้วยวิธีปกติธรรมดา ที่ดูด้วยตาเปล่าแล้วก็คงงงว่ามันผิดตรงไหน ช่องว่างก็ไม่มีแล้วแท้ๆ แต่ดันไม่ตรงกับคำที่ถูกต้องจริงๆ ซักที แสดงว่ามันต้องมีอักขระอะไรซักอย่างที่มองไม่เห็นด้วยตาเปล่าซ่อนอยู่แน่เลย (และไม่ใช่ตัวกระจอกๆ ที่สามารถจัดการได้ด้วย CLEAN ) ถ้างั้นเราลองมาพิสูจน์ด้วยการใช้ LEN นับจำนวนอักระใน Cell ดูดีกว่า

โอ้ว จะเห็นว่ามีอะไรไม่รู้เกินมาตั้ง 2 ตัว เพราะ LEN ได้ 14 ซึ่งมากกว่าคำที่ถูกต้องซึ่ง LEN ได้แค่ 12 อยู่ 2 ตัวนั่นเอง

ถึงเวลาท่ายาก

Concept คือ เราไม่แน่ใจว่าตัวประหลาดคือตัวไหน งั้นเราจะใช้อีก Concept นึงแทน นั่นคือ เก็บเอาไว้เฉพาะตัวที่เรารู้จักเท่านั้น ซึ่งใช้ความรู้เรื่อง Code ของ Character ที่ผมบอกไปตอนต้นนี่แหละ นั่นคือ

• เส้นวรรค คือ code 32
• ตัวเลข คือ code 48-57
• ตัวอักษรพิมพ์ใหญ่ภาษาอังกฤษ คือ code 65-90
• ตัวอักษรพิมพ์เล็กภาษาอังกฤษ คือ code 97-122
• ภาษาไทย คือ 161-249

ซึ่งแนวทางในการเก็บตัวที่ต้องการไว้ จะทำได้ 2 แนว คือ ใช้สูตร (แนะนำว่าควรมี Excel 365 ไม่งั้นจะยากเกินไป) กับ ใช้ Power Query ผมขอแนะนำวิธี Power Query ก่อน ซึ่งง่ายกว่า

วิธีใช้ Power Query

เอาข้อมูลที่เรา TRIM แล้วเข้า Power Query ซะ จากนั้นให้กด Add Custom Column ขึ้นมาแล้วใช้ฟังก์ชัน Text.Select มาช่วย (ใครอยากรู้ว่าลึกๆ ทำงานยังไงอ่านได้ที่นี่)

=Text.Select([TRIM],{"ก".."๙"}&{" "})

แค่นี้มันจะเก็บไว้เฉพาะอักขระที่เราระบุใน List เท่านั้น แค่นี้จบละ Close & Load ออกมาได้เลย

Tips : จริงๆ แล้ว Power Query ก็มี Clean กับ Trim นะ แต่ Trim แบบมาตรฐานของ Power Query ไม่ได้ตัด space ส่วนเกินระหว่างคำให้ (ถ้าจะให้ Trim ได้แบบ TRIM ของ Excel ต้องเขียน Custom Function อีกซึ่งน่าจะงงเกิน) ผมเลยแนะนำให้ตัดก่อนจะดีกว่า

วิธีเขียนสูตรของ Excel 365

ก่อนอื่น เราจะใช้ MID เพื่อสกัดเอาอักขระแต่ละตัวออกมาว่ามันเข้าเงื่อนไขที่อยากได้หรือไม่

ปล. ใครที่งงกับสูตรที่สามารถสร้างผลลัพธ์ออกมาทีเดียวหลายๆ ช่องได้แบบนี้ (เรียกว่า Array Formula) และอยากเรียนรู้เพิ่ม ผมมีเขียนบทความที่สามารถอ่านได้ฟรีเอาไว้ในระดับนึง และมีรับรองว่าจะเข้าใจได้ดีมากขึ้นแน่นอน

สมมติผมสนใจแค่ภาษาไทยกับ space ก็จะต้องมี Code เป็น 32 และ 161-249

ดังนั้นผมจะใช้ Boolean Logic มาช่วย คือ * แทน AND และ + แทน OR

=(CODE(MID(A12,SEQUENCE(LEN(A12)),1))>=161)*(CODE(MID(A12,SEQUENCE(LEN(A12)),1))<=249)+(CODE(MID(A12,SEQUENCE(LEN(A12)),1))=32)

จากนั้นผมจะใช้ IF ใส่เข้าไป โดยถ้าค่าออกมาไม่ใช่ 0 จะเอาอักขระตัวนั้นๆ ไว้ แต่ถ้าได้ 0 จะปล่อยเป็น Blank (“”) ไปซะ

=IF((CODE(MID(A12,SEQUENCE(LEN(A12)),1))>=161)*(CODE(MID(A12,SEQUENCE(LEN(A12)),1))<=249)+(CODE(MID(A12,SEQUENCE(LEN(A12)),1))=32),MID(A12,SEQUENCE(LEN(A12)),1),"")

ตรงไหนเป็น FALSE ให้เป็น Blank text ซะ (“”)

จากนั้นเราค่อยเอา TEXTJOIN มารวม

=TEXTJOIN("",TRUE,IF((CODE(MID(A12,SEQUENCE(LEN(A12)),1))>=161)*(CODE(MID(A12,SEQUENCE(LEN(A12)),1))<=249)+(CODE(MID(A12,SEQUENCE(LEN(A12)),1))=32),MID(A12,SEQUENCE(LEN(A12)),1),""))

พอเอามาเทียบกับคำที่ถูกต้อง มันก็ใช้ได้ละ (แต่ยากโคตรเห็นมะ 555)

สรุป

คิดว่าเป็นไงบ้างกับกำจัดอักขระที่ไม่ต้องการออกจากข้อความ ที่ผมแนะนำให้ครับ ใครอ่านแล้วได้ประโยชน์ อ่านแล้วทำตามไม่ได้ หรือว่ามีวิธีอะไรดีๆ อยากจะแชร์ก็ช่วย Comment บอกได้เลยนะครับ