ไปยังเนื้อหาหลัก

ตัดหรือลบอักขระที่ไม่ใช่ตัวเลขออกจากสตริงข้อความ

บางครั้งคุณอาจต้องลบอักขระที่ไม่ใช่ตัวเลขทั้งหมดออกจากสตริงข้อความและเก็บเฉพาะตัวเลขตามภาพด้านล่างที่แสดง บทความนี้จะแนะนำสูตรสำหรับแก้งานนี้ใน Excel


ตัดหรือลบอักขระที่ไม่ใช่ตัวเลขทั้งหมดออกจากสตริงข้อความด้วยสูตร

ใน Excel 2019 และ Office 365 ฟังก์ชัน TEXTJOIN ใหม่ที่รวมกับฟังก์ชัน IFERROR, MID, ROW และ INDIRECT สามารถช่วยให้คุณแยกเฉพาะตัวเลขจากสตริงข้อความได้ไวยากรณ์ทั่วไปคือ:

=TEXTJOIN("",TRUE,IFERROR(MID(text,ROW(INDIRECT("1:100")),1)+0,""))
  • text: สตริงข้อความหรือค่าเซลล์ที่คุณต้องการลบอักขระที่ไม่ใช่ตัวเลขทั้งหมดออก

1. โปรดคัดลอกหรือป้อนสูตรด้านล่างลงในเซลล์ว่างที่คุณต้องการให้ผลลัพธ์ออกมา:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

2. จากนั้นกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์แรกดูภาพหน้าจอ:

3. จากนั้นเลือกเซลล์สูตรจากนั้นลากที่จับเติมลงไปยังเซลล์ที่คุณต้องการใช้สูตรนี้มีการแยกเฉพาะตัวเลขและอักขระอื่น ๆ ที่ไม่ใช่ตัวเลขทั้งหมดถูกตัดออกดูภาพหน้าจอ:


คำอธิบายของสูตร:

แถว (ทางอ้อม ("1:100"): หมายเลข 1: 100 ในสูตรทางอ้อมหมายถึงฟังก์ชัน MID ประเมิน 100 อักขระของสตริงข้อความ อาร์เรย์นี้จะมีตัวเลข 100 ตัวดังนี้: {1; 2; 3; 4; 5; 6; 7; 8 .... 98; 99; 100}
หมายเหตุ: หากสตริงข้อความของคุณยาวขึ้นมากคุณสามารถเปลี่ยนตัวเลข 100 เป็นตัวเลขที่มากขึ้นได้ตามต้องการ

MID (A2, ROW (ทางอ้อม ("1: 100")), 1: ฟังก์ชัน MID นี้ใช้เพื่อดึงข้อความในเซลล์ A2 เพื่อให้ได้อักขระหนึ่งตัวและจะมีอาร์เรย์ดังนี้:
{"5"; "0"; "0"; ""; "K"; "u"; "t"; "o"; "o"; "l"; "s"; ""; "f" ; "o"; "r"; ""; "E"; "x"; "c"; "e"; "l"; ""; ";"; ";"; ";"; .. }

MID(A2,ROW(INDIRECT("1:100")),1)+0: การเพิ่มค่า 0 หลังจากใช้อาร์เรย์นี้เพื่อบังคับให้ข้อความเป็นตัวเลขค่าข้อความตัวเลขจะถูกแปลงเป็นตัวเลขและค่าที่ไม่ใช่ตัวเลขจะแสดงเป็นค่าความผิดพลาด #VALUE ดังนี้:
{"5"; "0"; "0"; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE! !; # VALUE! !; # VALUE! ... }

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: ฟังก์ชัน IFERROR นี้ใช้เพื่อแทนที่ค่าความผิดพลาดทั้งหมดด้วยสตริงว่างเช่นนี้:
{"5"; "0"; "0"; ""; ""; ""; ""; ""; ";"; "; …}

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): ในที่สุดฟังก์ชัน TEXTJION นี้จะรวมค่าที่ไม่ว่างทั้งหมดในอาร์เรย์ซึ่งส่งคืนโดยฟังก์ชัน IFFERROR และส่งกลับผลลัพธ์


หมายเหตุ / รายละเอียดเพิ่มเติม:

1. ด้วยสูตรข้างต้นตัวเลขจะถูกส่งกลับเป็นรูปแบบข้อความหากคุณต้องการค่าตัวเลขจริงโปรดใช้สูตรนี้อย่าลืมกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))+0

2. ใน Excel เวอร์ชันแรกสูตรนี้จะใช้ไม่ได้ในกรณีนี้สูตรต่อไปนี้อาจช่วยคุณได้โปรดคัดลอกหรือป้อนสูตรนี้ลงในเซลล์ว่าง:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)


ตัดหรือลบอักขระที่ไม่ใช่ตัวเลขทั้งหมดออกจากสตริงข้อความด้วยคุณสมบัติที่ง่าย

บางทีมันอาจจะยาวเกินไปที่จะจำสูตรข้างต้นฉันจะแนะนำ Kutools สำหรับ Excel สำหรับคุณด้วย ลบอักขระ คุณลักษณะนี้คุณสามารถลบอักขระที่เป็นตัวเลขตัวอักษรพิมพ์ไม่ได้หรือตัวอักษรผสมตัวเลขออกจากสตริงข้อความด้วยการคลิกเพียงไม่กี่ครั้ง คลิกเพื่อดาวน์โหลด Kutools for Excel ทดลองใช้ฟรี!


ฟังก์ชันสัมพัทธ์ที่ใช้:

  • TEXTJOIN:
  • ฟังก์ชัน TEXTJOIN รวมค่าหลายค่าจากแถวคอลัมน์หรือช่วงของเซลล์ที่มีตัวคั่นเฉพาะ
  • MID:
  • ฟังก์ชัน MID ใช้เพื่อค้นหาและส่งคืนอักขระจำนวนหนึ่งจากตรงกลางของสตริงข้อความที่กำหนด
  • ROW:
  • ฟังก์ชัน Excel ROW จะคืนค่าหมายเลขแถวของข้อมูลอ้างอิง
  • INDIRECT:
  • ฟังก์ชันทางอ้อมของ Excel จะแปลงสตริงข้อความเป็นการอ้างอิงที่ถูกต้อง
  • IFERROR:
  • ฟังก์ชัน IFERROR ใช้เพื่อส่งคืนผลลัพธ์ที่กำหนดเองเมื่อสูตรประเมินข้อผิดพลาดและส่งคืนผลลัพธ์ปกติเมื่อไม่มีข้อผิดพลาดเกิดขึ้น

บทความเพิ่มเติม:


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน  |  ลบแถวว่าง  |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล  |  รอบโดยไม่มีสูตร ...
ซุปเปอร์วีลุคอัพ: หลายเกณฑ์  |  หลายค่า  |  ข้ามหลายแผ่น  |  การค้นหาที่ไม่ชัดเจน...
โฆษณา รายการแบบหล่นลง: รายการแบบหล่นลงอย่างง่าย  |  รายการแบบหล่นลงขึ้นอยู่กับ  |  เลือกหลายรายการแบบหล่นลง...
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  เปรียบเทียบคอลัมน์กับ เลือกเซลล์เดียวกันและต่างกัน ...
คุณสมบัติเด่น: กริดโฟกัส  |  มุมมองการออกแบบ  |  บาร์สูตรใหญ่  |  สมุดงานและตัวจัดการชีต | ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)  |  เลือกวันที่  |  รวมแผ่นงาน  |  เข้ารหัส/ถอดรหัสเซลล์  |  ส่งอีเมลตามรายการ  |  ซุปเปอร์ฟิลเตอร์  |  ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ ... )  |  50 + แผนภูมิ ประเภท (แผนภูมิ Gantt ... )  |  40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด ... )  |  19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง ... )  |  12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน ... )  |  7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์ Excel ... )  |  ... และอื่น ๆ

Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...

รายละเอียด


แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)

  • หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
  • ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
  • เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
  • นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What is this formula for french settings?
This comment was minimized by the moderator on the site
Thanks for this. Nice formula.How would I alter it so that if the cell contains only letters the formula enters a 0 the results cell (rather than just blank as it is at the moment)?Thought I might be able to do it by wrapping the formula in another IF statement but I’m not getting very far.
This comment was minimized by the moderator on the site
Hello, Glenn,To display the results as blanks rather than zeros, please apply the following formula:=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Wow that’ll take some digesting 😋 Thanks for taking the time to reply 👍
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations