Thep Excel
CLEANลบอักขระขยะ
ลบตัวอักษรที่ไม่สามารถพิมพ์ได้ (ASCII 0-31) ออกจากข้อความ มีประโยชน์เมื่อคัดลอกข้อมูลจากระบบอื่นที่มีอักขระซ่อนอยู่
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| text | Text | ข้อความที่ต้องการล้างข้อมูล ชนิด Text หรือ Cell reference ที่มีข้อความ |
Additional Notes
ฟังก์ชัน CLEAN ใน Excel ใช้สำหรับลบตัวอักษรที่ไม่สามารถพิมพ์ได้ (Non-printable characters) ออกจากข้อความ โดยลบเฉพาะตัวอักษรในช่วง ASCII 0-31 เท่านั้น ซึ่งมักติดมาจากการคัดลอกข้อมูลจากระบบอื่นหรือเว็บไซต์
ที่เจ๋งคือ CLEAN ทำงานอย่างเงียบ ๆ คุณไม่เห็นเลยว่ามีอักขระขยะอยู่ แต่เมื่อนำข้อมูลไปใช้ต่อ ปัญหาก็จะไม่เกิด เช่น ข้อมูลที่คัดลอกมาจาก Database หรือ API มักมี Tab, Line Break, หรือตัวอักษรควบคุมอื่น ๆ ติดมา
ส่วนตัวผม แนะนำให้ใช้ CLEAN ทุกครั้งที่นำเข้าข้อมูลจากภายนอก แม้ว่าจะดูสะอาดตา แล้ว ลบอักขระขยะออกจะช่วยให้ VLOOKUP หรือ MATCH ทำงานได้อย่างถูกต้อง
How it works
ทำความสะอาดข้อมูล
ลบอักขระแปลกปลอมที่มองไม่เห็น ซึ่งทำให้การค้นหาข้อมูล (VLOOKUP) ผิดพลาด
Examples
ลบตัวอักษรขยะ (Tab Character)CLEAN(CHAR(9) & "Hello")
ลบอักขระ Tab (CHAR(9)) ออกจากข้อความ เหลือเพียง "Hello" เท่านั้น
ทำความสะอาดข้อมูลนำเข้าจาก DatabaseCLEAN(A2)
ถ้า A2 มีข้อมูล "John Doe" ที่มี Line Break หรืออักขระซ่อนติดมา CLEAN จะลบออกและเหลือเพียง "John Doe"
ใช้กับ TRIM เพื่อล้างข้อมูลให้สะอาดสั้นTRIM(CLEAN(A2))
รวม CLEAN และ TRIM เข้าด้วยกัน ให้ลบทั้งอักขระขยะและช่องว่างส่วนเกิน (นำหน้า ท้าย ระหว่าง)
แก้ปัญหา Line Break ในข้อมูลนำเข้าCLEAN("Line1" & CHAR(10) & "Line2")
CHAR(10) คือ Line Feed (ขึ้นบรรทัดใหม่) CLEAN ลบมันออก ทำให้ข้อความต่อกันติดกัน
FAQs
CLEAN ลบอักขระไหนบ้าง?+
CLEAN ลบอักขระ ASCII 0-31 (Non-printable characters) เช่น Tab (9), Line Feed (10), Carriage Return (13) เป็นต้น แต่ไม่ลบ ASCII 127 และ Unicode non-printing characters อื่น ๆ
ใช้กับ Excel เวอร์ชันไหนได้?+
ทุกเวอร์ชัน Excel ตั้งแต่ Excel 2003 ขึ้นไป รวม Excel 365, Excel 2021, Excel 2019
CLEAN ต่างจาก TRIM ยังไง?+
CLEAN ลบอักขระที่ไม่สามารถพิมพ์ได้ (ASCII 0-31) ส่วน TRIM ลบช่องว่างส่วนเกิน (spaces) ใช้คู่กันให้ล้างข้อมูลให้เรียบร้อย
CLEAN ลบ Unicode non-printing characters ได้ไหม?+
ไม่ได้ CLEAN ลบได้แค่ ASCII 0-31 เท่านั้น สำหรับ Unicode characters (127, 129, 141, 143, 144, 157) ต้องใช้วิธีอื่นเช่น SUBSTITUTE หรือ Regular Expression (ถ้าใช้ Power Query)
ใช้ CLEAN แล้ว VLOOKUP ยังหาไม่เจอ ทำยังไง?+
ลองใช้ TRIM(CLEAN()) ให้ช่องว่างส่วนเกินหายไปด้วย บางครั้งปัญหาอาจมาจากช่องว่างซ่อนมากกว่าอักขระขยะ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ CLEAN จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ASC แปลงตัวอักษรแบบเต็มความกว้าง (Full-width/Double-byte) ให้เป็นครึ่งความกว้าง (Half-width/Single-byte) ใช้กับข้อมูลจากภาษาญี่ปุ่น จีน หรือแหล่งข้อมูล DBCS อื่นๆ
CHAR แปลงรหัสตัวอักษร (1-255) เป็นตัวอักษรจริง มีประโยชน์สำหรับแทรกอักขระพิเศษที่พิมพ์ยาก เช่น การขึ้นบรรทัดใหม่
PHONETIC ดึงข้อความ Furigana (คำอ่านกำกับตัวอักษรคันจิภาษาญี่ปุ่น) ที่ฝังอยู่ในเซลล์ออกมาเป็นข้อความแยกต่างหาก ใช้ได้เฉพาะระบบที่ตั้งค่าภูมิภาคเป็นภาษาตะวันออกไกล
SUBSTITUTE ค้นหาคำเก่า (old_text) ในข้อความแล้วแทนที่ด้วยคำใหม่ (new_text) โดยแยกแยะตัวพิมพ์เล็ก-ใหญ่ (case-sensitive) และเลือกได้ว่าจะแทนที่ทุกจุดที่เจอ หรือเฉพาะลำดับที่ระบุผ่าน instance_num งานที่ใช้บ่อยคือลบตัวคั่นออกจากตัวเลขที่ถูกเก็บเป็นข้อความ เปลี่ยนโดเมนอีเมลหลังรีแบรนด์ หรือล้างอักขระที่พิมพ์มาผิดรูปแบบก่อนนำไปคำนวณต่อ
T ฟังก์ชันที่ตรวจสอบว่าค่าเป็นข้อความหรือไม่ ถ้าใช่ส่งกลับข้อความเดิม ถ้าไม่ใช่ส่งกลับค่าว่าง ใช้ปลอดภัยกว่าการต่อข้อความตรงๆ เมื่อไม่แน่ใจว่าเซลล์ต้นทางเก็บอะไรอยู่
TEXT ใช้รหัสรูปแบบ (Format Codes) เช่น "dd/mm/yyyy" สำหรับวันที่ หรือ "#,##0.00" สำหรับตัวเลขมีทศนิยม ผลลัพธ์ที่ได้จะเป็นข้อความ (Text) เสมอ ไม่สามารถนำไปคำนวณต่อได้
TRIM ลบช่องว่างที่ด้านหน้า ด้านหลัง และลดช่องว่างระหว่างคำให้เหลือเพียงเคาะเดียว เหมาะสำหรับทำความสะอาดข้อมูลที่ Copy จากเว็บหรือระบบอื่น
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่