Thep Excel
REGEXREPLACEแทนที่ข้อความด้วย Regular Expression
REGEXREPLACE ค้นหาและแทนที่ข้อความจากรูปแบบ Regular Expression ใช้สำหรับ Data Cleaning ที่ซับซ้อน เหมาะสำหรับ Excel 365 รองรับ backreferences
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| text | Text | ข้อความหรือเซลล์ที่ต้องการค้นหาและแทนที่ | |
| pattern | Text | รูปแบบ Regex ที่ต้องการค้นหา (ต้องอยู่ในเครื่องหมายคำพูด) เช่น “\d+” สำหรับตัวเลข “(\d{3})-(\d{4})” สำหรับ xxx-xxxx | |
| replacement | Text | ข้อความที่ต้องการแทนที่ สามารถใช้ $0 (ข้อความที่ตรงกันทั้งหมด), $1, $2, $3 (capture group) หรือ “” (ลบข้อความที่ค้นเจอ) | |
| [match_number]ไม่บังคับ | Number | -1 | [Optional] หมายเลขครั้งที่ต้องการแทนที่ (1 = ครั้งแรก, -1 = ทั้งหมด) ค่าเริ่มต้นคือ -1 (แทนที่ทั้งหมด) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
ฟังก์ชัน REGEXREPLACE ช่วยให้คุณค้นหาส่วนหนึ่งของข้อความและแทนที่ด้วยข้อความใหม่ โดยใช้รูปแบบ Regular Expression (Regex) ที่มีความยืดหยุ่นสูง เหมาะสำหรับงาน Data Cleaning ที่ซับซ้อน เช่น จัดรูปแบบเบอร์โทรศัพท์ ลบสัญลักษณ์พิเศษ หรือเปลี่ยนลำดับข้อมูล
ต่างจาก SUBSTITUTE ที่ค้นหาข้อความตรง ๆ REGEXREPLACE ใช้รูปแบบ (regex) ที่ยืดหยุ่นกว่า และสามารถใช้ backreferences ($1, $2) เพื่ออ้างอิงกลุ่มที่ค้นเจอได้ ทำให้สามารถจัดการข้อมูลที่ซับซ้อนได้อย่างมีประสิทธิภาพ
**⚠️ ความเข้ากันได้:** REGEXREPLACE เป็นฟังก์ชันใหม่ใน Excel 365 (Public Preview 2024) ถ้าใช้ Excel เวอร์ชั่นเก่ากว่า ต้องใช้ VBA หรือ Python in Excel แทน
**📚 เรียนรู้ Regex:** ศึกษาวิธีเขียน Regex แบบละเอียดได้ที่ [คู่มือ Regex ฉบับสมบูรณ์สำหรับ Excel](https://www.thepexcel.com/regex-excel-guide/) ซึ่งครอบคลุม Pattern พื้นฐาน, Capture Groups, Backreferences ($1, $2), Lookahead/Lookbehind และเทคนิคขั้นสูง
ส่วนตัวผม REGEXREPLACE เป็นฟังก์ชันที่ประหยัดเวลาเวลาต้องจัดรูปแบบข้อมูลจำนวนมากหรือลบสัญลักษณ์พิเศษ ทำให้ Data Cleaning เร็วกว่า SUBSTITUTE ใช้วิธีธรรมดาถึง 5-10 เท่า 😎
Examples
ตัวอย่างที่ 1: ลบสัญลักษณ์พิเศษจากเบอร์โทรREGEXREPLACE("0-2345-6789", "-", "")
ค้นหาเครื่องหมาย – และแทนที่ด้วยข้อความว่าง ("") เพื่อลบออกทั้งหมด ใช้งานง่าย ไม่ต้องใช้ capture group
ตัวอย่างที่ 2: จัดรูปแบบเบอร์โทรศัพท์ (Backreference)REGEXREPLACE("0234567890", "(\d{3})(\d{3})(\d{4})", "$1-$2-$3")
ใช้ capture groups () แบ่งตัวเลขออกเป็น 3 ส่วน: 3 หลัก-3 หลัก-4 หลัก แล้วใช้ $1, $2, $3 เพื่อนำกลับมาพร้อมเครื่องหมาย บ่อยมากในการจัดรูปแบบ
ตัวอย่างที่ 3: ลบตัวอักษรซ้ำออก (Advanced)REGEXREPLACE("aabbccdd", "(.)\\1", "$1")
ค้นหาตัวอักษร (.) ตามด้วยตัวเดิม \\1 (backreference ภายใน pattern) แล้วแทนที่ด้วยตัวเดิวตัวเดียว $1 ใช้ได้ดีสำหรับลบซ้ำ
ตัวอย่างที่ 4: เปลี่ยนรูปแบบวันที่ ISO เป็น ไทยREGEXREPLACE("2025-12-23", "(\d{4})-(\d{2})-(\d{2})", "$3/$2/$1")
แบ่ง YYYY-MM-DD เป็น 3 groups แล้วจัดเรียงใหม่เป็น DD/MM/YYYY ทำงานได้ทั้งเซลล์เดียวและกับ ARRAYFORMULA
FAQs
REGEXREPLACE ใน Google Sheets ต่างกันไหม?+
Google Sheets มี REGEXREPLACE ด้วย แต่ใช้ RE2 regex engine (Google's internal) ที่ไม่รองรับ lookahead/lookbehind ส่วน Excel ใช้ .NET Regex ที่รองรับได้ นอกนั้นก็ทำงานเกือบเหมือนกัน ต่างกันเพียง syntax backreference เล็กน้อย
ต่างจาก SUBSTITUTE อย่างไร?+
SUBSTITUTE ค้นหาข้อความตรง ๆ เท่านั้น เช่น "cat" ค้นหาแค่ "cat" ไม่ได้ "cats" ส่วน REGEXREPLACE ใช้รูปแบบ ค้นหา "cat." ได้ทั้ง "cat", "cats", "catch" เพราะ . = อักขระใด ๆ ยืดหยุ่นกว่ามาก
ไม่เก่ง Regex จะเขียนได้อย่างไร?+
ใช้ AI ช่วย! บอก ChatGPT/Claude/Gemini ว่า "Write a regex to [สิ่งที่ต้องการ]" เช่น "Write a regex to extract all email addresses" เขาจะให้ pattern มา หรือทดสอบที่ regexr.com ซึ่งมีคำอธิบายแบบ visual ทำให้เห็นผลทันที
Excel ใช้ Regex แบบไหน?+
ใช้ .NET Regex (Microsoft .NET Framework regular expressions) ที่รองรับ PCRE เต็มรูปแบบ รวม lookahead (?=), lookbehind (?
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ REGEXREPLACE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
FIND ค้นหาตำแหน่งเริ่มต้นของคำที่ต้องการภายในข้อความหลัก โดยสนใจตัวพิมพ์เล็ก-ใหญ่ (เช่น "A" ไม่เหมือนกับ "a") ถ้าค้นหาไม่เจอจะคืนค่า Error #VALUE! มักใช้ร่วมกับ MID, LEFT, RIGHT เพื่อตัดคำตามตำแหน่ง
LEFT ดึงตัวอักษรจากด้านซ้ายสุดของข้อความตามจำนวนที่ต้องการ ถ้าไม่ระบุจำนวนจะดึงมา 1 ตัว ผลลัพธ์เป็น Text เสมอแม้ดึงตัวเลขออกมา
MID ตัดข้อความออกจากตำแหน่งเริ่มต้นที่คุณกำหนด โดยระบุความยาวของข้อความที่ต้องการ สะดวกมากสำหรับดึงข้อมูลบางส่วนจากข้อความที่ยาว เช่น รหัสสินค้า, รหัสพนักงาน, หรือวันที่ที่ฝังตัวในข้อความ
REGEXEXTRACT เป็นฟังก์ชันสำหรับดึงข้อความย่อย (Substring) ที่ตรงกับรูปแบบ Regular Expression (Regex) ที่กำหนด เหมาะสำหรับการทำ Data Cleaning ขั้นสูง
REGEXTEST เป็นฟังก์ชันสำหรับทดสอบว่าข้อความตรงกับรูปแบบ Regular Expression ที่กำหนดหรือไม่ คืนค่า TRUE หรือ FALSE เหมาะสำหรับการตรวจสอบความถูกต้องของข้อมูล (Validation) เช่น ที่อยู่อีเมล เบอร์โทรศัพท์ หรือรูปแบบข้อมูลอื่น ๆ
REPLACE แทนที่ข้อความจากตำแหน่งที่กำหนด โดยระบุตำแหน่งเริ่มต้น จำนวนตัวอักษรที่ต้องการลบ และข้อความใหม่ที่จะใส่แทน แตกต่างจาก SUBSTITUTE ที่ค้นหาคำที่ตรงตามความพึงพอใจ REPLACE ใช้ตำแหน่งแน่นอน
RIGHT จะคืนค่าเป็นข้อความ (Text) ที่ถูกตัดมาจากด้านขวาสุดของข้อความต้นฉบับตามจำนวนที่ระบุ ถ้าไม่ระบุจำนวน ฟังก์ชันจะดึงมาเพียง 1 ตัวอักษร
.
ที่เจ๋งคือสามารถใช้ร่วมกับ LEN หรือ FIND เพื่อตัดข้อความแบบไดนามิกได้ ผลลัพธ์ที่ได้จะเป็น Text เสมอ ถ้าต้องการนำไปคำนวณต่อ ต้องแปลงเป็นตัวเลขก่อนครับ
SEARCH ค้นหาตำแหน่งของคำที่ต้องการในข้อความหลัก ถ้าเจอคืนตัวเลขตำแหน่งที่พบ ถ้าไม่เจอคืน #VALUE! ต่างจาก FIND ตรงที่ไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่ (A=a) และใช้ wildcard (* กับ ?) ค้นหาแบบมีรูปแบบได้ เอาไปต่อกับ ISNUMBER ก็กลายเป็นสูตรเช็คว่าเซลล์มีคำนี้อยู่หรือไม่ ซึ่งเป็นวิธีที่ใช้กันบ่อยที่สุดในงานจัดหมวดหมู่หรือกรองข้อมูลจากคำสำคัญ
SUBSTITUTE ค้นหาคำเก่า (old_text) ในข้อความแล้วแทนที่ด้วยคำใหม่ (new_text) โดยแยกแยะตัวพิมพ์เล็ก-ใหญ่ (case-sensitive) และเลือกได้ว่าจะแทนที่ทุกจุดที่เจอ หรือเฉพาะลำดับที่ระบุผ่าน instance_num งานที่ใช้บ่อยคือลบตัวคั่นออกจากตัวเลขที่ถูกเก็บเป็นข้อความ เปลี่ยนโดเมนอีเมลหลังรีแบรนด์ หรือล้างอักขระที่พิมพ์มาผิดรูปแบบก่อนนำไปคำนวณต่อ
TRIM ลบช่องว่างที่ด้านหน้า ด้านหลัง และลดช่องว่างระหว่างคำให้เหลือเพียงเคาะเดียว เหมาะสำหรับทำความสะอาดข้อมูลที่ Copy จากเว็บหรือระบบอื่น
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่