หลายคนมีความสัมพันธ์แบบรักและเกลียดกับเลขศูนย์นำหน้าใน Excelบางครั้งคุณต้องการมัน บางครั้งคุณไม่ต้องการมัน
แม้ว่า Excel จะถูกตั้งโปรแกรมให้ลบเลขศูนย์นำหน้าออกจากตัวเลขโดยอัตโนมัติ แต่ก็มีบางสถานการณ์ที่คุณอาจพบสถานการณ์เหล่านี้
ในบทช่วยสอน Excel นี้ ฉันจะแสดงให้คุณเห็นอย่างไรลบเลขศูนย์นำหน้าจากตัวเลขใน Excel
มาเริ่มกันเลย!
เนื้อหา
สาเหตุที่เป็นไปได้ของเลขศูนย์นำหน้าใน Excel
ดังที่ฉันได้กล่าวไปแล้ว Excel จะลบศูนย์นำหน้าทั้งหมดออกจากตัวเลขโดยอัตโนมัติตัวอย่างเช่น หากคุณป้อน 00100 ในเซลล์ใน Excel ระบบจะแปลงเป็น 100 โดยอัตโนมัติ
ในกรณีส่วนใหญ่ วิธีนี้สมเหตุสมผล เนื่องจากเลขศูนย์นำหน้าเหล่านั้นไม่สมเหตุสมผลเลย
แต่ในบางกรณีคุณอาจต้องการมัน
ต่อไปนี้คือสาเหตุบางประการที่ตัวเลขของคุณอาจยังคงเป็นศูนย์นำหน้า:
- หากตัวเลขถูกจัดรูปแบบเป็นข้อความ (โดยส่วนใหญ่จะเพิ่มเครื่องหมายอะพอสทรอฟีก่อนตัวเลข) ตัวเลขจะคงค่าศูนย์นำหน้าไว้
- เซลล์อาจถูกจัดรูปแบบให้แสดงตัวเลขที่มีความยาวเท่ากันเสมอหากจำนวนน้อยกว่านั้น ระบบจะเพิ่มศูนย์นำหน้าเพื่อชดเชยตัวอย่างเช่น คุณสามารถจัดรูปแบบเซลล์ให้แสดงตัวเลข 5 หลักได้ตลอดเวลา (หากตัวเลขน้อยกว่า 5 หลัก ระบบจะเพิ่มศูนย์นำหน้าโดยอัตโนมัติ)
วิธีที่เราเลือกลบเลขศูนย์นำหน้าขึ้นอยู่กับสาเหตุ
ดังนั้น ขั้นตอนแรกคือการระบุสาเหตุ เพื่อให้เราสามารถเลือกวิธีที่ถูกต้องในการลบเลขศูนย์นำหน้าเหล่านี้
วิธีลบเลขศูนย์นำหน้าออกจากตัวเลข
คุณสามารถลบเลขศูนย์นำหน้าออกจากตัวเลขได้หลายวิธี
ในส่วนนี้ ฉันจะแสดงให้คุณเห็นห้าวิธีดังกล่าว
แปลงข้อความเป็นตัวเลขพร้อมตัวเลือกการตรวจสอบข้อผิดพลาด
หากสาเหตุของตัวเลขนำหน้าคือมีคนเพิ่มเครื่องหมายอะพอสทรอฟีก่อนหน้านั้น (แปลงตัวเลขเหล่านั้นเป็นข้อความ) คุณสามารถใช้การตรวจสอบข้อผิดพลาดเพื่อแปลงตัวเลขกลับเป็นตัวเลขได้ในคลิกเดียว
นี่อาจเป็นวิธีที่ง่ายที่สุดในการกำจัดศูนย์นำหน้า
ที่นี่ฉันมีชุดข้อมูลที่มีตัวเลขที่มีเครื่องหมายอะพอสทรอฟีก่อนตัวเลขเหล่านี้และเลขศูนย์นำหน้านั่นเป็นสาเหตุที่คุณเห็นว่าตัวเลขเหล่านี้อยู่ชิดซ้าย (ในขณะที่ตัวเลขเริ่มต้นถูกจัดชิดขวา) และมีเลข 0 นำหน้าด้วย
ต่อไปนี้เป็นขั้นตอนในการลบเลขศูนย์นำหน้าออกจากตัวเลขเหล่านี้:
- เลือกตัวเลขที่จะลบศูนย์นำหน้าคุณจะสังเกตเห็นไอคอนสีเหลืองที่มุมบนขวาของส่วนที่เลือก
- คลิกไอคอนตรวจสอบจุดบกพร่องสีเหลือง
- คลิก "แปลงเป็นตัวเลข"
แค่นั้นแหละ!ขั้นตอนข้างต้นจะลบเครื่องหมายอะพอสทรอฟีและแปลงค่าข้อความเหล่านี้กลับเป็นตัวเลข
และเนื่องจาก Excel ถูกตั้งโปรแกรมให้ลบช่องว่างนำหน้าออกจากตัวเลขใดๆ ตามค่าเริ่มต้น คุณจะเห็นว่าการดำเนินการดังกล่าวจะลบศูนย์นำหน้าทั้งหมดโดยอัตโนมัติ
หมายเหตุ: วิธีนี้จะไม่ทำงานหากมีการเพิ่มศูนย์นำหน้าเป็นส่วนหนึ่งของรูปแบบตัวเลขที่กำหนดเองของเซลล์เพื่อจัดการกับสถานการณ์เหล่านี้ ให้ใช้วิธีการที่อธิบายไว้ถัดไป
เปลี่ยนรูปแบบตัวเลขแบบกำหนดเองของเซลล์
สาเหตุทั่วไปอีกประการหนึ่งที่ตัวเลขของคุณอาจแสดงเลขศูนย์นำหน้าคือเมื่อเซลล์ของคุณได้รับการจัดรูปแบบให้แสดงตัวเลขที่แน่นอนในแต่ละตัวเลขเสมอ
หลายคนต้องการให้ตัวเลขดูสม่ำเสมอและมีความยาวเท่ากัน ดังนั้นพวกเขาจึงระบุความยาวขั้นต่ำสำหรับตัวเลขโดยเปลี่ยนรูปแบบของเซลล์
ตัวอย่างเช่น ถ้าคุณต้องการให้ตัวเลขทั้งหมดแสดงเป็น 5 หลัก หากคุณมีตัวเลขที่มีเพียงสามหลัก Excel จะเพิ่มศูนย์นำหน้าสองตัวโดยอัตโนมัติ
ด้านล่าง ฉันมีชุดข้อมูลที่มีรูปแบบตัวเลขที่กำหนดเองซึ่งใช้เพื่อแสดงตัวเลขอย่างน้อยห้าหลักในเซลล์เสมอ
วิธีกำจัดเลขศูนย์นำหน้าเหล่านี้ก็แค่ลบการจัดรูปแบบที่มีอยู่ออกจากเซลล์
นี่คือขั้นตอนในการทำเช่นนี้:
- เลือกเซลล์ที่มีเลขศูนย์นำหน้า
- คลิกแท็บหน้าแรก
- ในกลุ่ม Numbers ให้คลิกเมนูดรอปดาวน์รูปแบบตัวเลข
- เลือก "ทั่วไป"
ขั้นตอนข้างต้นจะเปลี่ยนรูปแบบตัวเลขที่กำหนดเองของเซลล์ และตอนนี้ตัวเลขจะปรากฏตามที่คาดไว้ (โดยไม่มีศูนย์นำหน้าอยู่)
โปรดทราบว่าเทคนิคนี้จะใช้ได้ก็ต่อเมื่อสาเหตุของเลขศูนย์นำหน้าคือการจัดรูปแบบตัวเลขแบบกำหนดเองมันจะไม่ทำงานหากคุณใช้อะพอสทรอฟีในการแปลงตัวเลขเป็นข้อความ (ในกรณีนี้ คุณควรใช้วิธีการก่อนหน้านี้)
คูณด้วย 1 (โดยใช้เทคนิคการวางแบบพิเศษ)
เทคนิคนี้ใช้ได้ทั้งสองกรณี (โดยที่ตัวเลขถูกแปลงเป็นข้อความโดยใช้เครื่องหมายอะพอสทรอฟีหรือเมื่อมีการนำรูปแบบตัวเลขแบบกำหนดเองไปใช้กับเซลล์)
สมมติว่าคุณมีชุดข้อมูลด้านล่างและต้องการลบเลขศูนย์นำหน้าออกจากชุดข้อมูล
นี่คือขั้นตอนในการทำสิ่งนี้
- คัดลอกเซลล์ว่างในเวิร์กชีต
- เลือกเซลล์ที่มีตัวเลขที่คุณต้องการลบเลขศูนย์นำหน้า
- คลิกขวาที่ส่วนที่เลือกแล้วคลิกวางแบบพิเศษซึ่งจะเปิดกล่องโต้ตอบวางแบบพิเศษ
- คลิกตัวเลือกเพิ่ม (ในกลุ่มการดำเนินการ)
- คลิกตกลง
ขั้นตอนข้างต้นจะเพิ่ม 0 ให้กับช่วงของเซลล์ที่คุณเลือก และลบศูนย์และอะพอสทรอฟีนำหน้าทั้งหมด
แม้ว่าการดำเนินการนี้จะไม่เปลี่ยนค่าของเซลล์ แต่จะแปลงค่าข้อความทั้งหมดเป็นตัวเลข และคัดลอกการจัดรูปแบบจากเซลล์ว่างที่คุณคัดลอก (ซึ่งจะแทนที่การจัดรูปแบบที่มีอยู่ซึ่งทำให้ศูนย์นำหน้าปรากฏขึ้น)
วิธีนี้มีผลกับตัวเลขเท่านั้นหากมีสตริงข้อความในเซลล์ สตริงนั้นจะไม่เปลี่ยนแปลง
ใช้ฟังก์ชัน VALUE
อีกวิธีหนึ่งที่ง่ายและรวดเร็วในการลบเลขศูนย์นำหน้าคือการใช้ฟังก์ชันค่า
ฟังก์ชันนี้รับอาร์กิวเมนต์หนึ่งตัว (ซึ่งอาจเป็นข้อความหรือการอ้างอิงเซลล์พร้อมข้อความ) และส่งกลับค่าตัวเลข
นอกจากนี้ยังใช้กับทั้งสองกรณีที่ตัวเลขนำหน้าเป็นเครื่องหมายอะพอสทรอฟี (ใช้ในการแปลงตัวเลขเป็นข้อความ) หรือผลลัพธ์ของรูปแบบตัวเลขที่กำหนดเอง
สมมติว่าฉันมีชุดข้อมูลที่มีลักษณะดังนี้:
นี่คือสูตรการลบศูนย์นำหน้า:
= มูลค่า (A1)
หมายเหตุ: หากคุณยังคงเห็นเลขศูนย์นำหน้า คุณต้องไปที่แท็บหน้าแรกและเปลี่ยนรูปแบบเซลล์เป็นทั่วไป (จากเมนูแบบเลื่อนลงรูปแบบตัวเลข)
ใช้ข้อความเป็นคอลัมน์
ในขณะที่ใช้ฟีเจอร์ Text to Columns เพื่อแบ่งเซลล์ออกเป็นหลายคอลัมน์ คุณยังสามารถใช้เพื่อเอาศูนย์นำหน้าออกได้
สมมติว่าคุณมีชุดข้อมูลที่มีลักษณะดังนี้:
ต่อไปนี้เป็นขั้นตอนในการลบเลขศูนย์นำหน้าโดยใช้ข้อความเป็นคอลัมน์:
- เลือกช่วงของเซลล์ที่มีตัวเลข
- คลิกที่แท็บข้อมูล
- ในกลุ่มเครื่องมือข้อมูล คลิกข้อความเป็นคอลัมน์
- ในตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์ ให้ทำการเปลี่ยนแปลงต่อไปนี้:
- ขั้นตอนที่ 1 จาก 3: เลือก "แยก" แล้วคลิก "ถัดไป"
- ขั้นตอนที่ 2 จาก 3: ยกเลิกการเลือกตัวคั่นทั้งหมดแล้วคลิกถัดไป
- ขั้นตอนที่ 3 จาก 3: เลือกเซลล์เป้าหมาย (B2 ในกรณีนี้) และคลิก Finish
ขั้นตอนข้างต้นควรลบเลขศูนย์นำหน้าทั้งหมดและระบุเฉพาะตัวเลขเท่านั้นหากคุณยังคงเห็นเลขศูนย์นำหน้าอยู่ คุณต้องเปลี่ยนรูปแบบของเซลล์เป็น ทั่วไป (ซึ่งสามารถทำได้จากแท็บหน้าแรก)
วิธีลบเลขศูนย์นำหน้าออกจากข้อความ
แม้ว่าวิธีการข้างต้นทั้งหมดจะทำงานได้ดี แต่วิธีการเหล่านี้ใช้ได้กับเซลล์ที่มีค่าตัวเลขเท่านั้น
แต่ถ้าค่าตัวอักษรและตัวเลขหรือข้อความของคุณมีศูนย์นำหน้าด้วย
ในกรณีนี้ วิธีข้างต้นใช้ไม่ได้ผล แต่ต้องขอบคุณสูตรที่น่าทึ่งใน Excel ที่ทำให้คุณยังคงมีเวลา
สมมติว่าคุณมีชุดข้อมูลดังต่อไปนี้ และคุณต้องการลบศูนย์นำหน้าทั้งหมดออกจากชุดข้อมูลดังกล่าว:
นี่คือสูตรในการทำสิ่งนี้:
=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)
ให้ฉันอธิบายว่าสูตรนี้ทำงานอย่างไร
ส่วน SUBSTITUTE ของสูตรจะแทนที่ศูนย์ด้วยช่องว่างดังนั้นสำหรับค่า 001AN76 สูตรการแทนที่ให้ผลลัพธ์เป็น 1AN76
จากนั้น สูตร LEFT จะแยกอักขระที่อยู่ซ้ายสุดของสตริงที่เป็นผลลัพธ์ ซึ่งในกรณีนี้คือ 1
จากนั้น สูตร FIND จะค้นหาอักขระด้านซ้ายสุดที่กำหนดโดยสูตร LEFT และส่งคืนตำแหน่งในตัวอย่างของเรา สำหรับค่า 001AN76 จะให้ 3 (นั่นคือ ตำแหน่งของ 1 ในสตริงข้อความต้นฉบับ)
เพิ่ม 1 ลงในผลลัพธ์ของสูตร FIND เพื่อให้แน่ใจว่าเราแยกสตริงข้อความทั้งหมด (ยกเว้นเลขศูนย์นำหน้า)
จากนั้นลบผลลัพธ์ของสูตร FIND ออกจากผลลัพธ์ของสูตร LEN ซึ่งให้ความยาวของสตริงข้อความทั้งหมดสิ่งนี้ทำให้เราความยาวของการวนซ้ำข้อความโดยไม่มีศูนย์นำหน้า
ค่านี้จะใช้กับฟังก์ชัน RIGHT เพื่อแยกสตริงข้อความทั้งหมด (ยกเว้นเลขศูนย์นำหน้า)
หากคุณมีช่องว่างนำหน้าหรือต่อท้ายในเซลล์ ควรใช้ฟังก์ชัน TRIM สำหรับการอ้างอิงแต่ละเซลล์
ดังนั้น สูตรใหม่ที่เพิ่มฟังก์ชัน TRIM จะมีลักษณะดังนี้:
=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(LEFT(SUBSTITUTE(TRIM(A2),"0",""),1),TRIM(A2))+1)
ดังนั้น คุณสามารถใช้วิธีการง่ายๆ เหล่านี้เพื่อลบศูนย์นำหน้าออกจากชุดข้อมูลใน Excel
ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์!