แนะนำ สูตร Excel แยกตัวเลขออกจากตัวอักษร เช่น MID, TEXT, VALUE, FILTERXML และสูตรผสม พร้อมตัวอย่างและเทคนิคแก้ปัญหาที่เจอบ่อย Learn Excel formulas to extract numbers from text using MID, VALUE, FILTERXML, and combined functions with practical examples.
ทำไมต้อง แยกตัวเลขออกจากข้อความ ใน Excel
หลายครั้งที่เรามีข้อมูลผสมระหว่าง ตัวอักษร และ ตัวเลข เช่น รหัสสินค้า (A123), ที่อยู่ (บ้านเลขที่ 45/7), หรือข้อความที่มีตัวเลขฝังอยู่ หากต้องการนำตัวเลขเหล่านั้นไปคำนวณต่อ จำเป็นต้อง แยกตัวเลขออกจากข้อความ ซึ่ง Excel ไม่มีฟังก์ชันเดียวที่ทำได้ตรง ๆ แต่สามารถใช้ สูตร Excel แยกตัวเลขออกจากตัวอักษร ด้วยการผสมฟังก์ชันต่าง ๆ เข้าด้วยกัน
ใช้เมื่อใด และทำไมถึงสำคัญ
- แยก รหัสสินค้า ที่มีทั้งตัวอักษรและตัวเลข เช่น A1001 → 1001
- แยก ที่อยู่ เช่น “ซอย 22 ถนนหลัก” → 22
- แยก ตัวเลขจากข้อความยาว เพื่อนำไปคำนวณหรือกรองข้อมูล
- เตรียมข้อมูลสำหรับ การวิเคราะห์ หรือ การรายงาน
วิธีใช้ สูตร Excel แยกตัวเลขออกจากตัวอักษร
1) ใช้ MID + ROW + ISNUMBER
=TEXTJOIN("",TRUE,IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))สูตรนี้จะวนลูปดึงทุกตัวอักษร ถ้าเป็นตัวเลขจะถูกเก็บไว้ ผลลัพธ์: “12345”
2) ใช้ VALUE + SUBSTITUTE (กรณีรูปแบบชัดเจน)
=VALUE(SUBSTITUTE(A2,"ABC",""))ใช้เมื่อข้อความมีรูปแบบตายตัว เช่น “ABC123” → 123
3) ใช้ FILTERXML (Excel รุ่นใหม่)
=TEXTJOIN("",,FILTERXML(""&TEXTJOIN("",,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))&"","//s[.>=0 and .<=9]"))ใช้ XML parsing เพื่อดึงเฉพาะตัวเลขออกมา
4) ใช้ Power Query (ทางเลือกไม่ใช้สูตร)
ใน Power Query → ใช้ Extract → Digits เพื่อตัดตัวเลขออกมาโดยตรง
ปัญหาที่พบบ่อย + วิธีแก้
- สูตรยาวเกินไป: แนะนำใช้ Power Query หากข้อมูลมีจำนวนมาก
- ค่าที่ได้ไม่เป็นตัวเลข: ครอบด้วย
VALUE()เพื่อแปลงเป็นตัวเลขจริง - ข้อความซับซ้อน: ใช้สูตรผสมหลายชั้น เช่น MID+ISNUMBER+TEXTJOIN
ประโยชน์จากการใช้สูตรนี้
- ช่วยเตรียมข้อมูลสำหรับการวิเคราะห์ได้ง่าย
- นำตัวเลขไปใช้คำนวณต่อได้ทันที
- ลดเวลาในการกรองหรือแก้ไขข้อมูลด้วยมือ
ดาวน์โหลดไฟล์ตัวอย่าง
📥 ดาวน์โหลดไฟล์ตัวอย่างสูตร Excel แยกตัวเลขออกจากข้อความ
สรุป
การใช้ สูตร Excel แยกตัวเลขออกจากตัวอักษร เป็นทริคสำคัญที่ช่วยให้เราจัดการกับข้อมูลผสม (Alphanumeric) ได้สะดวกขึ้น ไม่ว่าจะเป็นรหัสสินค้า ข้อความ หรือที่อยู่ สูตรเหล่านี้สามารถประยุกต์ใช้ได้หลายรูปแบบ หากข้อมูลไม่ซับซ้อนอาจใช้ SUBSTITUTE() หรือ VALUE() แต่ถ้าข้อมูลหลากหลายควรใช้สูตรขั้นสูงหรือ Power Query การเข้าใจวิธีนี้จะทำให้คุณทำงานกับข้อมูลใน Excel ได้อย่างมืออาชีพมากขึ้น
