Regular Expressions (Regex) อาจดูซับซ้อนตอนแรก แต่มันเป็นเครื่องมือทรงพลังสำหรับ Pattern Matching และการจัดการข้อความ ใน Excel พวกเขาช่วยให้คุณค้นหา ดึงออกมา และแทนที่ข้อมูลตามรูปแบบที่ซับซ้อน ทำให้งานอย่างการทำความสะอาดข้อมูล การตรวจสอบความถูกต้อง และการแยกวิเคราะห์ มีประสิทธิภาพมากขึ้น พวกเขาสามารถประหยัดเวลาหลายชั่วโมงในการทำความสะอาด ตรวจสอบ และดึงข้อมูลออกมาได้เมื่อคุณเรียนรู้พื้นฐาน เพื่อรองรับ Regex Excel เสนอฟังก์ชันอย่าง REGEXTEST, REGEXEXTRACT และ REGEXREPLACE
นี่คือ Regex Crash Course ของเราสำหรับ Excel Power Users
Regex คืออะไร?
Regex (Regular Expressions) เป็นภาษา Pattern สำหรับข้อความ แทนที่จะค้นหาหรือจับคู่คำที่แน่นอน Regex ให้คุณอธิบาย "รูปร่าง" ของข้อความ ใน Excel Regex เหมือน FIND + MID + SUBSTITUTE ที่มีพลังมากขึ้น โดย Pattern เดียวสามารถ Validate, Extract หรือ Clean ข้อความได้
คุณสามารถจับคู่โครงสร้างทั่วไปใน Excel ด้วย Regex ได้: ที่อยู่อีเมล หมายเลขโทรศัพท์ วันที่ในหลายรูปแบบ หรือ ID ใดๆ ที่ปฏิบัติตามกฎ
ใน Excel สมัยใหม่ คุณใช้มันหลักกับ:
- REGEXTEST (ตรวจสอบว่า Pattern มีอยู่หรือไม่)
- REGEXEXTRACT (ดึงส่วนที่จับคู่)
- REGEXREPLACE (แทนที่/ทำความสะอาดส่วนที่จับคู่)
พื้นฐาน Regex: การสร้าง Patterns
Regex Patterns เป็น Strings ที่กำหนดสิ่งที่คุณมองหา พวกเขารวมตัวอักษร Literal (เช่น "abc") กับ Metacharacters พิเศษ คุณสามารถใช้ตารางนี้เป็น Quick Reference
Metacharacters และ Syntax สำคัญ:
| Building Block | ความหมาย | ตัวอย่าง Pattern ด่วน | จับคู่ |
|---|---|---|---|
| abc | ข้อความ Literal | TX- | TX- |
| . | ตัวอักษรเดี่ยวใดๆ | a.c | abc, a-c |
| \d | ตัวเลข (0–9) | \d{4} | 2026 |
| \D | ไม่ใช่ตัวเลข | \D+ | TX- |
| \w | Word char (A–Z, a–z, 0–9, _) | \w+ | amina_01 |
| \W | ไม่ใช่ Word char | \W+ | – ( ) . |
| \s | Whitespace | \s+ | spaces/tabs |
| \S | ไม่ใช่ Whitespace | \S+ | [email protected] |
| [ABC] | หนึ่งในตัวอักษรเหล่านี้ | [AEIOU] | A |
| [A-Z] | ช่วง (ตัวใหญ่) | [A-Z]{2} | TX |
| [a-z] | ช่วง (ตัวเล็ก) | [a-z]+ | rahman |
| [0-9] | ช่วงตัวเลข | [0-9]+ | 1054 |
| [^0-9] | NOT เหล่านี้ (Negated class) | [^0-9]+ | TX- |
| * | 0 หรือมากกว่า | \d* | "", 7, 1054 |
| + | 1 หรือมากกว่า | \d+ | 7, 1054 |
| ? | Optional (0 หรือ 1) | -? | – หรือ nothing |
| {n} | พอดี n ครั้ง | \d{2} | 12 |
| {n,} | อย่างน้อย n ครั้ง | \d{4,} | 202601 |
| {n,m} | ระหว่าง n ถึง m | \d{2,4} | 12, 2026 |
| ^ | เริ่มต้นข้อความ | ^TX- | TX-1054 (ที่เริ่ม) |
| $ | สิ้นสุดข้อความ | \.com$ | ลงท้าย .com |
| \b | Word boundary | \bTX-\d{4}\b | TX-1054 เป็น Token เดียว |
| ( … ) | Capturing group | (\d{4}) | จับ 1054 |
| (?: … ) | Non-capturing group | (?:TX|CA)-\d{4} | TX-1054 หรือ CA-1054 |
| \ | Escape ตัวอักษรพิเศษ | company\.com | company.com ตรงตัว |
| .* | ตัวอักษรใดๆ (Greedy) | \(.*\) | จับจาก ( แรก ถึง ) สุดท้าย |
| .*? | ตัวอักษรใดๆ (Non-greedy) | \(.*?\) | จับ ( … ) ที่เล็กที่สุด |
| (?i) | Case-insensitive mode (ไม่รองรับใน Excel Regex Patterns; ใช้ Argument case_sensitivity ของฟังก์ชันแทน) | — | ใช้ REGEXTEST(text, pattern, 1) |
- Quantifiers (*, +, ?, {}) นำไปใช้กับ Element ก่อนหน้า
- Flags: ฟังก์ชัน Regex ของ Excel ไม่รองรับ Inline Flags เช่น (?i) ใน Pattern ใช้ Argument ของฟังก์ชันสำหรับ Case Sensitivity (เช่น Argument ที่ 3 ของ REGEXTEST) หรือใช้ Character Classes (เช่น [Aa])
- Greedy vs. Lazy: โดยค่าเริ่มต้น Quantifiers เป็น Greedy (จับคู่มากที่สุด) เพิ่ม ? หลัง Quantifier เพื่อทำให้ Lazy (เช่น .*?)
ฟังก์ชัน Regex ของ Excel
Excel เสนอฟังก์ชัน Regex 3 ตัวในตัว ฟังก์ชันเหล่านี้รับ String ข้อความและ Regular Expression Pattern เป็นอินพุต และ Validate หรือ Transform ข้อความตาม Pattern มารู้จักแต่ละฟังก์ชันก่อนลงลึกในคอร์ส
ไวยากรณ์:
=REGEXTEST(text, pattern, [case_sensitivity])
ฟังก์ชันนี้ตรวจสอบว่า Pattern จับคู่กับส่วนใดส่วนหนึ่งของข้อความที่ให้มาหรือไม่ ส่งคืน TRUE หาก Pattern จับคู่ที่ไหนที่หนึ่งในข้อความ FALSE ถ้าไม่จับคู่
- text: ข้อความหรือการอ้างอิงเซลล์ที่คุณต้องการทดสอบ
- pattern: Regular Expression (Regex) Pattern ที่อธิบายข้อความที่คุณต้องการจับคู่
- [case_sensitivity]: กำหนดว่า Match เป็น Case-sensitive หรือไม่ โดยค่าเริ่มต้น Match เป็น Case-sensitive
- 0: Case sensitive
- 1: Case insensitive
ฟังก์ชันนี้ส่งคืน TRUE หาก A1 มีตัวเลข 3 ตัวติดกัน
ไวยากรณ์:
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
ฟังก์ชันนี้ดึงข้อความที่จับคู่
- [return_mode]: ตัวเลขที่ระบุ Strings ที่คุณต้องการดึง โดยค่าเริ่มต้น Return Mode คือ 0
- 0: ส่งคืน String แรกที่จับคู่กับ Pattern
- 1: ส่งคืน Strings ทั้งหมดที่จับคู่เป็น Array
- 2: ส่งคืน Capturing Groups จากการจับคู่แรกเป็น Array
=REGEXEXTRACT(A1, "\d{3}-\d{3}-\d{4}")
ดึงหมายเลขโทรศัพท์อย่าง "123-456-7890"
ไวยากรณ์:
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
ฟังก์ชันนี้แทนที่ส่วนที่จับคู่ด้วยข้อความใหม่
- replacement: ข้อความที่คุณต้องการใช้แทน Pattern คุณสามารถอ้างอิง Capture Groups ด้วย $1, $2, ฯลฯ
- occurrence: ระบุ Instance ไหนของ Pattern ที่คุณต้องการแทนที่ โดยค่าเริ่มต้น Occurrence คือ 0 ซึ่งแทนที่ทุก Instance ตัวเลขลบแทนที่ Instance นั้น นับจากท้าย
=REGEXREPLACE(A1, "\d{3}", "***")
ซ่อนลำดับตัวเลข 3 ตัวทุกชุด
ฟังก์ชันเหล่านี้จะ Spill หากส่งคืน Array (เช่น การดึงหลายครั้ง)
ตัวอย่างปฏิบัติสำหรับ Excel Power Users
สมมติว่าคุณมีข้อมูลสับสน มาใช้ Regex กับสถานการณ์ทั่วไป
ตัวอย่างที่ 1: การตรวจสอบที่อยู่อีเมล
คุณสามารถใช้ REGEXTEST เพื่อ Flag อีเมลบริษัทที่ถูกต้อง (Case-insensitive Matches มักปลอดภัยกว่า)
ใช้ Pattern ต่อไปนี้เพื่อ Validate อีเมล
- Pattern: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
=REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")
ใช้ Conditional Formatting ไฮไลท์อีเมลที่ไม่ถูกต้อง:
- ไปที่แท็บ Home >> เลือก Conditional Formatting >> เลือก New Rule
- เลือก Use a formula to determine which cells to format
- ใส่สูตรต่อไปนี้
- เลือก Format >> เลือกสี Fill >> คลิก OK
อีเมลที่ไม่ถูกต้องจะถูกไฮไลท์สีแดง
คุณยังสามารถใช้ REGEXTEST เพื่อ Flag อีเมลบริษัทที่ถูกต้อง
=REGEXTEST(B2,"@company\.com$",1)
- Argument ที่ 3 (1) ทำให้ Match เป็น Case-insensitive ดังนั้น Company.com ก็ผ่าน
- \. Escape จุด จุดธรรมดา . หมายถึง "ตัวอักษรใดๆ"
ตัวอย่างที่ 2: การดึงข้อมูลจาก Notes ที่สับสน
สมมติว่าคุณมีข้อมูลผสม เช่น Order ID หรือหมายเลขโทรศัพท์ ใน Notes คุณสามารถดึงหมายเลขโทรศัพท์หรือ Order IDs ด้วยฟังก์ชัน REGEXEXTRACT
ดึง Order ID:
=REGEXEXTRACT(D2,"TX-\d{4}")
- ถ้าบางแถวไม่มี ให้ห่อด้วย IFERROR:
=IFERROR(REGEXEXTRACT(D2,"TX-\d{4}"),"")
ดึงหมายเลขโทรศัพท์:
- Data: "Call me at 123-456-7890 or (987) 654-3210."
- Pattern: (\d{3}[-. )]+){2}\d{4}
=REGEXEXTRACT(A2, "(\d{3}[-. )]+){2}\d{4}", 0, 1)
สูตรนี้ดึงหมายเลขโทรศัพท์จากข้อความ
ตัวอย่างที่ 3: มาตรฐานหมายเลขโทรศัพท์โดยลบ Non-Digits
- สร้างเวอร์ชันตัวเลขอย่างเดียวก่อน
=REGEXREPLACE(C2,"[^\d]","")
- ตอนนี้คุณจัดรูปแบบได้ (ตัวอย่าง: 10 ตัวท้ายเป็น US Number เมื่อมี):
=LET(x,REGEXREPLACE(C2,"[^\d]",""), d,RIGHT(x,10), IF(LEN(d)=10,"("&LEFT(d,3)&") "&MID(d,4,3)&"-"&RIGHT(d,4),""))
วิธีนี้ลบตัวอักษรที่ไม่ใช่ตัวเลขทั้งหมด และจัดรูปแบบหมายเลขโทรศัพท์ที่ได้
ตัวอย่างที่ 4: การแยกวิเคราะห์วันที่และจัดรูปแบบใหม่
คุณสามารถแยกวันที่และจัดรูปแบบใหม่ด้วย Regex
- Pattern: (\d{1,2})/(\d{1,2})/(\d{4})
=REGEXREPLACE(A2, "(\d{1,2})/(\d{1,2})/(\d{4})", "$3-$2-$1")
สูตรนี้แยกวันที่และจัดรูปแบบใหม่เป็นรูปแบบ ISO-like ที่ถูกต้อง คุณยังสามารถใช้ Capture Groups () เพื่ออ้างอิงส่วนต่างๆ ในการแทนที่
ตัวอย่างที่ 5: ทำความสะอาดข้อมูลสับสน (เช่น ลบช่องว่างเกิน)
คุณสามารถใช้ Regex ทำความสะอาดข้อมูลสับสน เช่น ลบช่องว่างเกินและปรับรูปแบบให้ปกติ
เพื่อลบช่องว่างเกิน ใช้ Pattern ต่อไปนี้
- Pattern: \s+
=REGEXREPLACE(A2, "\s+", " ")
สูตรนี้แทนที่ช่วง Whitespace ด้วยช่องว่างเดียว
ตัวอย่างที่ 6: ลบสิ่ง gìในวงเล็บ (รวมวงเล็บ)
- เลือกเซลล์และใส่สูตรต่อไปนี้:
=REGEXREPLACE(D2,"\s*\(.*?\)","")
- .*? เป็นการจับคู่ Non-greedy มันหยุดที่วงเล็บปิดแรก แทนที่จะเป็นวงเล็บปิดสุดท้าย
สูตรนี้ลบข้อมูลทั้งหมดในวงเล็บ รวมถึงวงเล็บเอง
ตัวอย่างที่ 7: กรองแถวที่มี Order ID
=FILTER(A2:F16,REGEXTEST(D2:D16,"TX-\d{4}"))
สูตรนี้กรองข้อมูลตาม Regex Pattern
เทคนิค Regex ขั้นสูงใน Excel
- Lookaheads/Lookbehinds: ยืนยันเงื่อนไขโดยไม่ Consume ข้อความ
- Positive Lookahead: (?=…) เช่น \d+(?=%)$ สำหรับตัวเลขก่อน % Sign
- ใช้ใน REGEXEXTRACT เพื่อดึงราคา: \d+\.\d{2}(?=\sUSD)
- Non-Capturing Groups: (?:…) สำหรับจัดกลุ่มโดยไม่ Capture
- Alternation: สำหรับตัวเลือก เช่น (http|https)://\S+ สำหรับ URLs
- การจัดการ Arrays: หาก REGEXEXTRACT ส่งคืนหลาย Groups ใช้ INDEX หรือ Spill Ranges
- การรวมกับฟังก์ชันอื่น: Nest กับ IF, FILTER, หรือ LAMBDA สำหรับ Workflows ที่ทรงพลัง
เคล็ดลับและแนวทางปฏิบัติที่ดี
- ทดสอบ Patterns: ใช้เครื่องมือออนไลน์อย่าง regex101.com ด้วย Flavor "ECMAScript" (ใกล้เคียง Regex ของ Excel มากที่สุด)
- ประสิทธิภาพ: Regex อาจช้าบนชุดข้อมูลใหญ่ ทดสอบบนตัวอย่างก่อน
- ข้อผิดพลาด: หากไม่มี Match REGEXEXTRACT ส่งคืน #N/A จัดการด้วย IFNA หรือ IFERROR
- ข้อจำกัด: Regex ของ Excel 基于 Subset ไม่มี PCRE Support เต็มรูปแบบ หลีกเลี่ยงฟีเจอร์ขั้นสูงอย่าง Recursion
- เรียนรู้เพิ่มเติม: ฝึกกับชุดข้อมูลจริง Regex จะง่ายขึ้นด้วยการใช้งาน!
ดาวน์โหลด Practice Workbook
สรุป
Excel Power Users สามารถทำตาม Regex Crash Course ของเราเพื่อเร่งการทำความสะอาดข้อมูล การตรวจสอบ และ Automation เมื่อคุณคล่องกับ Building Blocks พื้นฐานแล้ว ให้สำรวจ Lookaheads และ Lookbehinds สำหรับ Pattern Matching ที่ซับซ้อนกว่า Crash Course นี้เริ่มต้นให้คุณ: เข้าใจ Expression ก่อน แล้วทดลอง Patterns ใน Workbook ว่าง Regex สามารถเปลี่ยนวิธีที่คุณจัดการข้อความใน Excel!
