เคยเจอไหมครับ ก๊อปปี้รายชื่อจากไลน์กลุ่มหรือเฟซบุ๊กมาวางใน Excel หน้าตาข้อความก็ดูเหมือนเดิมทุกอย่าง แต่พอเอาไปเทียบด้วย VLOOKUP หรือ XLOOKUP กลับหาไม่เจอซะอย่างนั้น ทั้งที่พิมพ์ชื่อถูกทุกตัวอักษร ปัญหานี้ส่วนใหญ่เกิดจาก \”ช่องว่างแฝง\” หรืออักขระที่พิมพ์ไม่ได้ (non-printable characters) ที่ติดมากับข้อมูลโดยไม่รู้ตัว บทความนี้จะพาไปรู้จักสูตร TRIM และ CLEAN ที่ช่วยแก้ปัญหานี้ได้ตรงจุด
ทำไมปัญหาช่องว่างแฝงถึงพบบ่อย
เวลาเราก๊อปปี้ข้อความจากเว็บไซต์ แอปแชท หรือไฟล์ระบบอื่นมาวางใน Excel ข้อมูลที่ติดมาด้วยมักมีสิ่งที่ตาเรามองไม่เห็น เช่น เว้นวรรคซ้ำหลายครั้งระหว่างคำ ช่องว่างหลงเหลืออยู่หน้าหรือหลังข้อความ หรืออักขระควบคุม (control character) ที่มากับการจัดฟอร์แมตของระบบต้นทาง ข้อความแบบนี้เวลาดูด้วยตาจะเหมือนกันเป๊ะกับข้อความที่พิมพ์เอง แต่ในมุมของ Excel มันคือคนละค่ากัน ทำให้สูตรเปรียบเทียบหรือค้นหาข้อมูลพลาดโดยไม่รู้สาเหตุ
TRIM คืออะไร ใช้งานยังไง
TRIM คือสูตรที่ลบช่องว่างส่วนเกินออกจากข้อความ ทั้งช่องว่างต้น-ท้ายข้อความ และเว้นวรรคซ้ำระหว่างคำ โดยจะเหลือไว้แค่ช่องว่างเดี่ยวคั่นระหว่างคำเท่านั้น รูปแบบสูตรคือ
=TRIM(text)
=TRIM(A2)| A | B | |
|---|---|---|
| 1 | ชื่อที่ก๊อปมา | ผลลัพธ์หลัง TRIM |
| 2 | สมชาย ใจดี | สมชาย ใจดี |
จากตัวอย่าง เซลล์ A2 มีช่องว่างหน้าชื่อ ช่องว่างเกินระหว่าง \”สมชาย\” กับ \”ใจดี\” และช่องว่างท้ายชื่ออีก พอใช้ TRIM ครอบไว้ จะเหลือแค่ \”สมชาย ใจดี\” ที่มีช่องว่างคั่นคำเดียวพอดี ดูสะอาดขึ้นทันที
CLEAN คืออะไร ใช้งานยังไง
ถ้า TRIM จัดการเรื่องช่องว่าง CLEAN ก็คือสูตรที่จัดการเรื่องอักขระที่พิมพ์ไม่ได้ (non-printable characters) ซึ่งมักติดมาตอนก๊อปปี้ข้อมูลจากระบบอื่น เช่น ไฟล์ที่ export มาจากโปรแกรมบัญชี หรือหน้าเว็บบางประเภท อักขระพวกนี้มองด้วยตาเปล่าไม่เห็น แต่จะไปแทรกอยู่ในข้อความและทำให้สูตรเทียบค่าผิดพลาดได้ รูปแบบสูตรคือ
=CLEAN(text)
=CLEAN(A2)| A | B | |
|---|---|---|
| 1 | ข้อมูลจากระบบอื่น | ผลลัพธ์หลัง CLEAN |
| 2 | รหัสสมาชิก045[อักขระแฝง] | รหัสสมาชิก045 |
ในตัวอย่างนี้ เซลล์ A2 มีอักขระควบคุมแฝงอยู่ท้ายรหัสสมาชิกซึ่งติดมาจากระบบต้นทาง แม้หน้าตาจะดูเหมือนมีแค่ \”รหัสสมาชิก045\” แต่ Excel จะมองว่าเป็นคนละค่ากับที่พิมพ์เอง พอใช้ CLEAN ครอบไว้ อักขระแฝงเหล่านี้จะถูกลบออก เหลือแต่ข้อความที่ใช้เปรียบเทียบได้ตรงจริง
ตัวอย่างใช้งานจริง: ทำทะเบียนสมาชิกจากรายชื่อที่ก๊อปจากไลน์กลุ่ม
สมมติกำลังรวบรวมรายชื่อสมาชิกกลุ่มไลน์เพื่อทำทะเบียนใน Excel แล้วใช้ VLOOKUP เช็คว่าใครจ่ายเงินแล้วบ้าง แต่ปรากฏว่าบางชื่อค้นหาไม่เจอทั้งที่พิมพ์ถูก เพราะตอนก๊อปปี้จากไลน์มา ชื่อมีช่องว่างแฝงติดมาด้วย
=TRIM(CLEAN(A2))| A | B | C | |
|---|---|---|---|
| 1 | ชื่อจากไลน์กลุ่ม | สถานะ | ชื่อที่ใช้เทียบจริง |
| 2 | วิภา สุขใจ | รอตรวจสอบ | วิภา สุขใจ |
การซ้อนสูตร =TRIM(CLEAN(A2)) แบบนี้ช่วยให้มั่นใจได้ว่าข้อความในคอลัมน์ C สะอาดทั้งเรื่องช่องว่างและอักขระแปลกปลอมในขั้นตอนเดียว จากนั้นค่อยเอาคอลัมน์ C ไปใช้กับ VLOOKUP หรือ XLOOKUP ต่อได้อย่างมั่นใจว่าจะไม่พลาดเพราะข้อมูลไม่ตรงกันแบบมองไม่เห็นอีก หลักการนี้ใช้ได้เหมือนกันกับการเอาข้อมูลรายรับรายจ่ายที่ก๊อปมาจากเว็บธนาคารหรือแอปอื่นมาทำบัญชีส่วนตัวใน Excel ก่อนจะเอาไปสรุปยอดหรือเทียบรายการซ้ำด้วย Remove Duplicates ก็ควร TRIM และ CLEAN ให้เรียบร้อยก่อนเสมอ ไม่งั้นรายการที่ควรซ้ำกันอาจไม่ถูกตรวจพบเพราะมีช่องว่างแฝงต่างกันอยู่
ข้อควรระวังที่พบบ่อย
1. TRIM ไม่ลบ non-breaking space
อักขระ non-breaking space (Unicode 160) ที่มักติดมาจากการก๊อปปี้ข้อมูลจากหน้าเว็บ หน้าตาเหมือนช่องว่างปกติทุกอย่าง แต่ TRIM จะไม่ลบให้ เพราะ TRIM ถูกออกแบบมาให้ลบเฉพาะช่องว่างมาตรฐาน (Unicode 32) เท่านั้น ถ้าเจอกรณีนี้ต้องใช้ SUBSTITUTE ช่วยแปลง non-breaking space ให้เป็นช่องว่างปกติก่อน แล้วค่อย TRIM ต่อ เช่น =TRIM(SUBSTITUTE(A2,CHAR(160)," "))
2. ลำดับการใช้ TRIM กับ CLEAN คู่กัน
ถ้าข้อมูลมีทั้งช่องว่างเกินและอักขระพิมพ์ไม่ได้ปนกัน แนะนำให้ซ้อนสูตรใช้ CLEAN ก่อนแล้วค่อย TRIM ครอบอีกชั้น คือ =TRIM(CLEAN(A2)) เพราะบางครั้งอักขระที่พิมพ์ไม่ได้เมื่อถูกลบออกไปแล้วอาจเหลือช่องว่างซ้ำที่ต้อง TRIM อีกรอบพอดี การเรียงลำดับแบบนี้ช่วยให้ข้อความสะอาดครบทั้งสองปัญหาในสูตรเดียว
3. ผลลัพธ์ของ TRIM/CLEAN ยังเป็นข้อความ ไม่ใช่ตัวเลข
ถ้าเอาไปใช้ทำทะเบียนแล้วต้องเทียบกับข้อมูลอื่น ควรใช้ค่าที่ผ่าน TRIM/CLEAN แล้วเป็นตัวหลัก แทนที่จะใช้ข้อมูลดิบที่ยังไม่ได้ทำความสะอาด
สรุป
TRIM และ CLEAN เป็นสูตรเล็กๆ ที่ช่วยแก้ปัญหาใหญ่ได้ โดยเฉพาะเวลาต้องเอาข้อมูลที่ก๊อปปี้มาจากไลน์ เฟซบุ๊ก หรือเว็บไซต์อื่นมาทำทะเบียนหรือบัญชีใน Excel ถ้าใช้ร่วมกันแบบซ้อนสูตร =TRIM(CLEAN(A2)) จะช่วยให้ข้อมูลสะอาดพร้อมใช้งานกับสูตรค้นหาอย่าง VLOOKUP และ XLOOKUP ได้แม่นยำขึ้น ไม่ต้องมานั่งงงว่าทำไมพิมพ์ถูกแล้วแต่หาไม่เจอ ลองนำไปปรับใช้กับข้อมูลที่มีอยู่ตอนนี้ดูได้เลยครับ
