เทคนิคการใช้งาน Regular Expression บน Excel

by
Post Views: 2,279

เมื่อต้องการเลือกข้อมูล หรือจัดกลุ่มข้อมูลที่เป็นข้อความ ยาวๆ ซับซ้อน ก็ต้องคิดถึงการใช้ Regular Expression ซึ่งใช้งานได้ทั่วไปใน Shell Script ต่างๆ แต่เมื่อต้องทำสิ่งเหล่านี้ใน Excel นั้น ค่อนข้างยุ่งยาก  แล้วในที่สุด ก็ไปพบคนใจดี เขียน VBS มาให้ ตามไฟล์นี้ regexp.txt ให้ใช้งาน Regular Express แบบเป็น Formula ง่ายๆ

วิธีการใช้ติดตั้งใช้งาน

  1. Download ไฟล์ข้างต้นไป แล้วแก้เป็นนามสกุล .bas
  2. เปิด Excel แล้วกดปุ่ม Alt-F11
  3. ไปที่ File > Import File แล้วเลือกไฟล์ .bas ข้างต้น
  4. ผลคือ จะได้ Module ชื่อ regexp
  5. ใน Excel ตั้งแต่ 2007 เป็นต้นไป จะไม่ยอมให้ Excel ใช้งานพวก Macro ต่างๆได้ ดังนั้น ให้ Save เป็นนามสกุล .xlsm จึงจะใช้งานต่อไปได้

ใน Module นี้ จะได้ฟังก์ชั่นมา 3 อย่างคือ

  1. RegExp : หาว่าใน String นั้นมี Pattern ที่ค้นหาหรือไม่
  2. RegReplace: แทนที่คำที่ค้นหา
  3. RegExtract: แกะเอาเฉพาะ pattern ที่ต้องการออกมา

ตัวอย่างที่ใช้งานจริง คือการแกะเอา Parameter บางอย่าง จาก URL ที่เก็บมา เพื่อเอาไปประมวลผลต่อด้วย PSPP หรือ SPSS

สมมุติใน Cell “A1” มี URL ดังนี้

/webmail/src/right_main.php?PG_SHOWALL=0&newsort=0&startMessage=1&mailbox=INBOX

ใน Cell “B1” ต้องการดูว่า newsort ตั้งค่าเป็นอะไร ก็เขียนสูตรว่า

=RegExExtract(“.*newsort=([0-9])&.*”,A1,”$1″)

ผลที่ได้คือ 0

หรือ Cell “C1” ต้องการดูค่าว่า mailbox ตั้งค่าอะไรอยู่ ก็ใช้สูตรว่า
=RegExExtract(“.*mailbox=([a-zA-Z0-9._-]*)&.*”,A1,”$1″)

Reference

http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function