สิ่งหนึ่งที่เป็นจุดอ่อนของ Excel ในปัจจุบัน ก็คือความสามารถในการหาข้อความที่มี Pattern ตามต้องการ ใน Excel มีแค่ Wildcard (* กับ ?) ซึ่งไม่ค่อยยืดหยุ่นเท่าไหร่ โดยเฉพาะถ้าเอาไปเทียบกับการใช้ Regular Expression (เรียกสั้นๆว่า Regex) ซึ่งเป็นวิธีที่ตอบโจทย์กว่าการใช้สัญลักษณ์ Wildcard ใน Excel อย่างเทียบไม่ติด
ถ้าเราใช้ Regular Expression เป็น เราก็จะสามารถค้นหาข้อมูลที่มีลักษณะตามรูปแบบ (Pattern) ตามที่เรากำหนดได้ เช่น จะหา email, เบอร์โทรศัพท์, ชื่อ นามสกุล หรืออะไรก็ตามในข้อความ ขอแค่มันมี Pattern ที่แน่นอนก็พอ
ที่น่าคับแค้นใจคือในโปรแกรมอย่าง Google Sheets หรือ Python สามารถดรียกใช้ Regex ได้ค่อนข้างสะดวก แต่ถ้าจะใช้ Regex ใน Excel เราจะต้อง import Library พิเศษผ่าน VBA ก่อน (ต้อง Add Reference ชื่อ Microsoft VBScript Regular Expressions) ซึ่งเป็นเรื่องที่ค่อนข้างยุ่งยากสำหรับคนทั่วไป ดังนั้นใครอยากให้ Excel มี Regex ในตัวเลยโดยไม่ต้องใช้ VBA สามารถไป Vote ได้ที่นี่
ในบทความนี้จะเป็นการสอนใช้ Regular Expression เบื้องต้น ซึ่งความรู้นี้สามารถนำไปใช้ได้กับหลายโปรแกรมเลยที่รองรับ Regular Expression ดังนั้นเรียนรู้ไปแล้วคุ้มแน่นอน (เดี๋ยวเราจะมาทำ Regex กันจริงๆ ใน Excel VBA, Google Sheets, Python ในตอนต่อไปนะครับ)
สารบัญ
เริ่มเรียนรู้ Regular Expression
วิธีการเรียนรู้ Regular Expression ได้จากหลายเว็บ เช่น
- https://regexone.com/
- https://medium.com/factory-mind/regex-tutorial-a-simple-cheatsheet-by-examples-649dc1c3f285
- https://cheatography.com/davechild/cheat-sheets/regular-expressions/
แต่ถ้าใครขี้เกียจอ่านในเว็บอื่น ลองอ่านที่ผมสรุปเบื้องต้นข้างล่างนี้ก็ได้
พอเรียนแล้ว สามารถลองเอานำคำที่เราต้องการ Match ไปลองใส่ในเว็บ https://regexr.com/ แล้วลองใส่ pattern ต่างๆ ลงไปดูว่ามันเจออะไรบ้าง ข้อดีคือนอกจากมันจะตอบอบ่างรวดเร็วว่าเจออะไรบ้าง ตรงไหน มันยังอธิบาย Pattern ที่เราเขียนให้ด้วยว่าหมายความว่ายังไง
Syntax พื้นฐาน
Literals
=========
abcกขค… ตัวหนังสือ(ที่ระบุ)
123… ตัวเลข(ที่ระบุ)
Special Characters
===================
. อักขระอะไรก็ได้ (ยกเว้นขึ้นบรรทัดใหม่)
^… เริ่มด้วย…
…$ จบด้วย…
* มีอย่างน้อย 0 ตัวขึ้นไป
+ มีอย่างน้อย 1 ตัวขึ้นไป
? มี 0 หรือ 1 ตัว
\ escape character สำหรับอักขระพิเศษ เช่น \. ก็จะแปลว่าเครื่องหมายจุด หรือ \* ก็จะแปลว่าเครื่องหมาย *
Character Classes (Alternation ระดับ Character)
==================
[abc] a, b, หรือ c
[^abc] ไม่ใช่ a, b, c
[a-z] a ถึง z ตัวไหนก็ได้
[A-Z] A ถึง Z ตัวไหนก็ได้
[0-9] เลข 0 ถึง 9
Predefined Character Classes
=======================
\d ตัวเลขอะไรก็ได้ เทียบเท่า [0-9]
\D อักขระอะไรก็ได้ที่ไม่ใช่ตัวเลข
\w อักขระภาษาอังกฤษหรือตัวเลข เทียบเท่า [a-zA-Z0-9_]
\W ไม่ใช่อักขระภาษาอังกฤษหรือตัวเลข
\s whitespace character (space, tab, newline)
\S อะไรก็ได้ที่ไม่ใช่ whitespace character
Quantifiers
========================
* มีอย่างน้อย 0 ตัวขึ้นไป
+ มีอย่างน้อย 1 ตัวขึ้นไป
? มี 0 หรือ 1 ตัว
{n} มีจำนวน n ครั้ง
{n,} มีจำนวน n ครั้งขึ้นไป
{n,m} มีจำนวน n-m ครั้ง
Grouping and Capturing
========================
(…) capturing group
(?:…) non-capturing group
Anchors
================
^… เริ่มด้วย…
…$ จบด้วย…
\b word boundary
\B non-word boundary
| หรือ (Alternation ระดับ Pattern)
============
(abc|def) หา abc หรือ def
a(|b)c หาเจอทั้ง ac หรือ abc เลย
Flags พิเศษ
=======
i ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่
g global search (หาทุกตัว ไม่ใช่แค่ตัวแรก).
m multiline matching เช่น ทำให้ ^ and $ เจอทั้ง start และ end of a line ไม่ใช่ข้อความรวมกันทั้งหมดตัวอย่าง Pattern และความหมาย
- thep = มีคำว่า thep ตรงไหนก็ได้ในข้อความ
- ^thep = ขึ้นต้นด้วย thep (คำว่า thep ต้องอยู่หน้าสุด)
- thep$ = จบด้วยคำว่า thep (คำว่า thep ต้องอยู่หลังสุด)
- ^thep$ = เป็นคำว่า thep เป๊ะๆ ห้ามมีคำอื่น
- [thep] = มีตัว t h e หรือ p
- thep[1-5] = มีคำว่า thep ตามด้วยเลข 1-5 (ตัวเดียว)
- thep\d = มีคำว่า thep ตามด้วยเลข 0-9 (ตัวเดียว)
- thep\d{5} = มีคำว่า thep ตามด้วยเลข 0-9 (5 digit)
- thep\d{5,7} = มีคำว่า thep ตามด้วยเลข 0-9 (5-7 digit)
- thep\d{5,} = มีคำว่า thep ตามด้วยเลข 0-9 (5 digit ขึ้นไป)
- thep\D = มีคำว่า thep ตามด้วยอะไรก็ได้ที่ไม่ใช่ตัวเลข 1 ตัว
- thep. = มี thep ตามด้วยตัวอะไรก็ได้ 1 ตัว
- thep\. = มีคำว่า thep. (thepตามด้วยจุด) อยู่ในข้อความ
- th.p = มี th ตามด้วยตัวอะไรก็ได้ 1 ตัว แล้วตามด้วย p
- th.+p = มี th ตามด้วยตัวอะไรก็ได้อย่างน้อย 1 ตัว แล้วตามด้วย p
- th.?p = มี th ตามด้วยตัวอะไรก็ได้ 0 หรือ 1 ตัว (มีหรือไม่มีก็ได้) แล้วตามด้วย p
- th.*p = มี th ตามด้วยตัวอะไรก็ได้กี่ตัวก็ได้ (มีหรือไม่มีก็ได้) แล้วตามด้วย p
- thep? = มีคำว่า the ตามด้วย p หรือไม่ก็ได้
- thep+ = มีคำว่า the ตามด้วย p อย่างน้อย 1 ตัว
- th(ep)+ = มีคำว่า th ตามด้วย ep อย่างน้อย 1 ชุด
- (thep|inw)excel = มีคำว่า thepexcel หรือ inwexcel
- \bthep\b = เจอ thep เมื่อไม่ได้ติดกับคำอื่นเท่านั้น แต่ดีกว่า \sthep\s เพราะ \bthep\b จะเจอ thep ตอนขึ้นต้นประโยคเลยด้วย
รูปแบบที่ซับซ้อนขึ้น
สมมติข้อความที่ผมสนใจคืออันนี้ และผมต้องการดึงเฉพาะส่วนที่เป็น email ออกมา ดูสิว่าจะทำยังไง?
สวัสดี ผมชื่อ นายศิระ เอกบุตร ชื่อเล่น ระ อายุ 37 ปี สูง 170 cm มี email คือ thepexcel@gmail.com ชอบการ์ตูนเรื่อง Hunter x Hunter, Attack on Titan, Evangelion ชอบหนังเรื่อง The Matrix, Inception ชอบเล่นเกม ROV, Valorant, StarCraft2 ลืมบอกไปว่าผมเป็นคนทำเว็บ www.thepexcel.com เพื่อแบ่งปันความรู้เรื่อง Excel & Power BI ยินดีที่ได้รู้จักครับการจะเลือกเอาสิ่งที่ต้องการ เราก็ต้องถามตัวเองว่า ถ้าเรามองด้วยตาเปล่า เรารู้ได้ยังไงว่าส่วนไหนของ email?
ถ้าถามผม มันต้องเป็นประมาณนี้
มีอักขระจำนวนนึง ตามด้วยเครื่องหมาย @ แล้วต่อด้วยอักขระอักจำนวนนึง ตามด้วยจุด แล้วตามอักขระอีกจำนวนนึง โดยต้องมีเครื่องหมายเว้นวรรคมาครอบทั้งหน้าหลังด้วย
เครื่องหมายที่เกี่ยวข้อง เพื่อที่อยากจะได้ email ตั้งแต่ @ จนถึง .
@.+\.- @ คือ ตัว @ (เราอยากจะหาตัวอะไรก็พิมพ์ตัวนั้น ยกเว้นจะไปซ้ำกับสัญลักษณ์พิเศษ ซึ่งต้องใส่ \ นำหน้าจะได้รู้ว่าเป็นอักขระธรรมดา)
- .+ คืออักขระอะไรก็ได้อย่างน้อย 1 ตัว
- . แทนอักขระอะไรก็ได้ (ที่ไม่ใช่ขึ้นบรรทัดใหม่)
- + คือมีอย่างน้อย 1 ตัว
- \. แทนจุด (\ คือ ใส่ไปให้รู้ว่าต้องการ . ธรรมดา ไม่ใช่สัญลักษณ์พิเศษ)
ปรากฏว่ามัน Match ข้อมูลเยอะมาก เพราะเราดันบอกว่าให้เอาตั้งแต่เครื่องหมาย @ แล้วตามด้วยตัวอะไรก็ได้อย่างน้อย 1 ตัว (เพราะใช้+) แล้วก็ตามด้วยด้วยจุด มันก็เล่น Match ให้เยอะที่สุดเท่าที่จะ Match ได้เลย อันนี้คือธรรมชาติของมันที่เรียกว่า Greedy Match (Match แบบตะกละ)
ถ้าเราต้องการให้มันหาให้น้อยที่สุดเท่าที่จำเป็น เราสามารถใส่เครื่องหมาย ? ต่อท้าย + เข้าไป (เราใส่ ? ต่อท้ายพวก * + ? ได้ เพื่อให้มันทำการหาแบบ Non Greedy )ซึ่งจะได้ผลว่าเจอถึงจุดตัวแรกที่ต่อจากเครื่องหมาย @ เท่านั้น (เพราะหาแบบไม่ตะกละแล้ว)
@.+?\.
อย่างไรก็ตามเราสามารถใช้เทคนิคอื่นได้ เช่น
- \s คือ เว้นวรรค
- \S คือ ทุกอย่างที่ไม่ใช่เว้นวรรค
ดังนั้นผมสามารถแก้ Pattern เป็นแบบนี้ได้ โดยที่ไม่ต้องใช้ ? เลย เพราะผมไม่เอาเว้นวรรค มันเลยไม่ข้ามไปที่คำอื่น(ข้ามไม่ได้เพราะมีเว้นวรรคคั่นอยู่)
@\S+\.
พอรู้แบบนี้ก็จะสามารถใช้ Pattern นี้เพื่อเอา Email ทั้งอันได้
\S+@\S+\.\S+
ถ้าในข้อความมี email หลายที่มันก็จะสามารถหาเจอได้ เช่น ข้อความกลายเป็นแบบนี้ ก็ไม่มีปัญหา
xyz@gmail.com สวัสดี ผมชื่อ นายศิระ เอกบุตร ชื่อเล่น ระ อายุ 37 ปี สูง 170 cm abc@outlook.com เป็นคนทำเว็บ www.thepexcel.com เพื่อแบ่งปันความรู้เรื่อง Excel & Power BI มี email คือ thepexcel@gmail.com ชอบการ์ตูนเรื่อง Hunter x Hunter, Attack on Titan, Evangelion xxxx@aaa.co.th ชอบหนังเรื่อง The Matrix, Inception ชอบเล่นเกม ROV, Valorant, StarCraft2 ยินดีที่ได้รู้จักครับ thepexceltraining@gmail.com
ถ้าหากเราอยากจะได้ส่วนข้างหน้า @ และหลัง @ แยกกัน เราสามารถใส่วงเล็บเข้าไปแบ่ง Group ผลลัพธ์ได้ ดังนี้
(\S+)@(\S+\.\S+)
หรือจะใส่วงเล็บใหญ่ครอบไปอีกที เพื่อเอาทั้ง email เป็นอีก Group ด้วยก็ได้ดังนี้
((\S+)@(\S+\.\S+))
