เทคนิคลบ “ช่องว่าง” ใน Excel

“อยากลบช่องว่างใน Excel ต้องทำยังไงคะ?”
“ใช้ฟังก์ชัน TRIM ได้เลยครับ”
“ใช้แล้ว แต่ช่องว่างยังอยู่ค่ะ”

“เห!”

ช่องว่าง (จากการเคาะ spacebar) คือตัวอักษรแบบหนึ่ง นั่นคือตัวอักษรลำดับที่ 32 ตามมาตรฐาน ASCII (อ่านว่า แอส-กี)

ถ้าเขียนด้วยสูตร Excel คือ

=CHAR(32)

ช่องว่างแบบนี้ ศัพท์เทคนิคเรียกว่า Whitespace

Whitespace ถูกลบได้ด้วยฟังก์ชัน TRIM หรือเขียนสูตรเป็น

=TRIM(A2)

(สมมติข้อมูลอยู่ในเซลล์ A2)

ฟังก์ชัน TRIM จะลบช่องว่างด้านหน้าและหลัง ไม่ว่าช่องว่างนั้นจะเกิดจากการเคาะ spacebar กี่ครั้งก็ตาม
(ถ้าเป็นช่องว่างระหว่างคำจะถูกลบเหลือหนึ่ง)

แต่มีตัวอักษรอีกแบบนึงที่หน้าตาเหมือนช่องว่าง แต่ไม่ใช่ช่องว่าง นั่นคือตัวอักษรลำดับที่ 160

ช่องว่างแบบนี้ ศัพท์เทคนิคเรียกว่า Non-breaking space (NBSP, บางคนเรียกว่า required space, hard space, fixed space)

Non-breaking space ไม่ได้เกิดจากการเคาะ spacebar แต่เกิดจากการเขียนสูตร
ถ้าเขียนด้วยสูตร Excel ฟังก์ชันที่ใช้คือ

=CHAR(160)

ปัญหาคือ ฟังก์ชัน TRIM ลบ Non-breaking space ไม่ได้!

จากภาพ ถ้าดูด้วยตาจะพบว่าตัวอักษรในคอลัมน์ A (รหัสไปรษณีย์) ควรมี 5 อักขระ

แต่เมื่อนับด้วยฟังก์ชัน LEN จะพบว่ามี 7 หรือ 6 อักขระ (คอลัมน์ B) แสดงว่ามีช่องว่างอยู่ด้านหน้าและ/หรือหลัง

ถ้าครอบด้วยฟังก์ชัน TRIM แล้วนับด้วย LEN จะพบว่าอักขระยังคงมีมากกว่า 5 (คอลัมน์ C)

แปลว่า TRIM ลบช่องว่างไม่ได้ เพราะช่องว่างนั้นคือ Non-breaking space

“แล้วจะลบยังไง?”

วิธีการลบ Non-breaking space ต้องใช้ฟังก์ชัน SUBSTITUTE โดยแทนที่ CHAR(160) ด้วยช่องว่างปกติ (Whitespace)
หรือเขียนสูตรเป็น

=SUBSTITUTE(A2,CHAR(160),” “)

แล้วใช้ร่วมกับ TRIM เพื่อลบ Whitespace ไปพร้อมกันเลย โดยปรับสูตรเป็น (คอลัมน์ E)

=TRIM( SUBSTITUTE(A2,CHAR(160),” “) )

(ผลลัพธ์จากฟังก์ชัน SUBSTITUTE จะได้ data type เป็น text เสมอ)

(ถ้าอยากให้ data type เป็น number ให้ใส่เครื่องหมาย – – นำหน้า เช่น =- -TRIM(SUBSTITUTE(A2,CHAR(160),” “))

ถ้าทดสอบโดยครอบด้วยฟังก์ชัน LEN จะพบว่าได้ 5 อักขระตามต้องการ (คอลัมน์ D)

เย้! ลบได้แล้ว 😊

“อ้าว! แล้ว Non-breaking space มาอยู่ในข้อมูลนี้ได้ยังไง?”

ก่อนจะตอบคำถาม เราต้องมาทำความเข้าใจกับ Non-breaking space เสียก่อน

ประโยชน์ของ Non-breaking space คือป้องกันการขึ้นบรรทัดใหม่แบบไม่ถูกต้อง

โดยปกติแล้ว ข้อมูลที่เราพิมพ์ลงไปในคอมพิวเตอร์ ถ้ายาวเกิน 1 บรรทัดจะถูกตัดเพื่อขึ้นบรรทัดใหม่ตรงช่องว่างระหว่างคำ

ปัญหาคือ การขึ้นบรรทัดใหม่อาจทำให้ความหมายเปลี่ยนไป

เช่น คำว่า Excel 365

ถ้าพิมพ์คำว่า Excel บรรทัดนึง แต่ 365 อยู่อีกบรรทัดนึง อาจทำให้ความหมายเปลี่ยนไป

ดังนั้น ถ้าต้องการให้ Excel 365 อยู่ด้วยกันเสมอ ช่องว่างตรงนี้ต้องเป็น Non-breaking space

“อืมม์.. แล้ว Non-breaking space มาอยู่ในข้อมูลนี้ได้ยังไงล่ะ?”

ลองนึกดี ๆ ว่าเราได้ข้อมูลนี้มาจากไหน?

เราไม่ได้พิมพ์เอง แต่ก๊อปปี้มาจากระบบ หรือก๊อปปี้มาจากอินเทอร์เน็ต (ใช่ไหมล่ะ ^^)

ในอินเทอร์เน็ตใช้ภาษา HTML และภาษา HTML ก็มักใช้ Non-breaking space เพื่อป้องกันการตัดคำแบบไม่ถูกต้อง

“แต่ข้อมูลนี้ไม่ได้ก๊อปปี้มาจากเน็ต ทำไมถึงมี Non-breaking space ล่ะ?”

ข้อมูลนี้ คุณอาจไม่ได้ก๊อปปี้มาจากเน็ต แต่ได้มาจากคนอื่น!

เป็นไปได้ว่า คนอื่นอาจก๊อปปี้มาจากอินเทอร์เน็ต แล้วส่งต่อให้คุณ ก็เลยมี Non-breaking space ติดมาตั้งแต่ตอนนั้น

อีกสาเหตุที่เป็นไปได้คือ ก๊อปปี้ข้อมูลมาจากระบบ แต่ข้อมูลในระบบผิด เพราะถูกอัปโหลดแบบผิดวิธี

เมื่อเตรียมข้อมูลบางอย่างเพื่อจะอัปโหลดขึ้นระบบ ข้อมูลนั้นอาจเป็นตัวเลขที่มีเลขศูนย์นำหน้า เช่น เบอร์โทรศัพท์ เลขทะเบียนนิติบุคคล

ตอนอัปโหลด บางระบบอาจถูกออกแบบไม่ดี ทำให้มองว่าข้อมูลนี้เป็นตัวเลข จึงตัดเลขศูนย์นำหน้าทิ้งไป

เช่น เลขทะเบียนนิติบุคคลต้องมี 13 หลัก แต่ระบบกลับตัดเลขศูนย์นำทิ้ง ทำให้เหลือ 12 หลัก ผลคืออัปโหลดไม่ได้

โปรแกรมเมอร์บางคนจึงแก้ปัญหาโดยนำเลขทะเบียนนิติบุคคลไปเชื่อมกับ Non-breaking space เช่น

=A2&CHAR(160)

ผลคืออัปโหลดได้ แต่ข้อมูลในระบบผิด

และเมื่อข้อมูลในระบบผิด พอเกิดการก๊อปปี้ ผลคือผิดกันเป็นทอด ๆ

มันก็เป็นเช่นนี้แล..

ถ้าอยากให้ข้อมูลไม่มี Non-breaking space การพิมพ์เองน่าจะชัวร์ที่สุด (อย่าไปก๊อปปี้จากที่อื่น)

หรืออาจแก้ด้วยสูตร TRIM(SUBSTITUTE(A2,CHAR(160),” “)) ก็ได้เช่นกัน 🙂

ใครเคยเจอปัญหาเรื่อง Non-breaking space เมนต์บอกกันหน่อยนะครับ 😊

Reference: https://en.wikipedia.org/wiki/Non-breaking_space?fbclid=IwAR3HoBjUyIuwk–3E3f9j7SfVFhjNEiWC_VSbIB74Gpqt-B-TQTu-Xo8eS0

วิศวกรรีพอร์ต

คนธรรมดาผู้มีประสบการณ์ทำงานหลากหลายตำแหน่ง คลุกคลีกับการทำรีพอร์ตมาโดยตลอด สุดท้ายค้นพบแนวทางของตัวเอง จึงอยากแบ่งปันเคล็ดลับและประสบการณ์ให้กับผู้สนใจ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.