↓Skip to main content

แยกข้อมูลที่อยู่เป็นหลายๆ cell ด้วย Google Sheets

·1 min
Google Sheets Spreadsheet Regular Expression
Table of Contents

พอดีวันนี้ผมไปเจอคนถามใน group เกี่ยวกับ Excel ว่าเราจะแยกข้อมูลที่อยู่ออกมาเป็น cell แขวง เขต จังหวัด รหัสไปรษณีย์ ได้ยังไงบ้าง

จากตัวอย่างข้อมูลในโจทย์เราจะพอเห็น pattern อยู่บ้างนะครับว่ามันมีการลำดับข้อมูลดังนี้

[บ้านเลขที่] [ห้อง] [หมู่ที่] [ถนน] [แขวง/ตำบล] [เขต/อำเภอ] [จังหวัด] [รหัสไปรษณีย์]

ครบบ้างไม่ครบบ้างแล้วแต่ cell แต่ที่แน่ๆมันเรียงตามนี้ เพียงเท่านี้เราก็พอเห็นหนทางแล้วครับ ถ้าใครเขียนโปรแกรมอยู่แล้วน่าจะมองออกว่าของแบบนี้เราน่าจะใช้ Regular Expression มาช่วยได้ใช่ไหมล่ะ โชคดีของคนที่ใช้ Google Sheets คือเรามีคำสั่งที่แยก text โดยใช้ Regular Expression ได้ซึ่งคำสั่งนั้นก็คือ

REGEXEXTRACT() #

คำสั่งที่จะทำให้การแยก text ของคุณออกจากกันมันง่ายขึ้น โดยโครงสร้างคำสั่งหน้าตาเป็นแบบนี้ครับ

=REGEXEXTRACT("text", "regular expression")

Regular Expression #

พูดถึง Regular Expression หรือ RegExp หลายรอบหลายคนที่ไม่รู้จักมันคนจะงงใช่ไหมครับ งั้นเดี๋ยวมาทำความรู้จักมันก่อน ไม่งั้นเราก็จะใช้คำสั่งด้านบนไม่ได้นะครับ แต่เนื่องจาก RegExp เนี่ยมันค่อนข้างจะยาว ผมจะแนะนำให้ไปอ่านบทความนี้ก่อนนะครับ คนนี้เขียนไว้ละเอียดยิบเลย

Regular Expression เช็ก/จัด/ตัด/แบ่งstring แค่บอกมาว่าอยากได้อะไร

อ่านจบแล้วใครอยากลองทดสอบความเข้าใจเล่นก็ต้องก็มาลองได้ที่เว็บนี้ครับ ผมใช้บ่อยมาก

RegEx101

เริ่มเลยนะครับ #

ผมจะอนุมานไปว่าทุกคนได้อ่านและทำความเข้าใจ RegExp อย่างดีแล้ว ดังนั้นตอนนัี้ผมจะพาใช้ =REGEXEXTRACT() แล้วนะ มาเริ่มดึงกันทีละจุดเลย

รหัสไปรณีย์ #

อันนี้ง่ายสุดครับเป็นตัวเลข 5 ตัวอยู่ท้ายสุดเสมอ

REGEXEXTRACT(A3:A,"\d{5}$"))

แขวง/ตำบล #

สังเกตง่ายๆครับมันจะมี แขวง หรือ ตำบล หรือ ต. นำหน้าเสมอ ดังนั้นตัวอักษรภาษาไทยที่ตามหลังมาน่ะใช่หมดแน่ๆ

REGEXEXTRACT(A3:A,"แขวง[ก-๙]+|ตำบล[ก-๙]+|ต\.[ก-๙]+")

ที่ผมไม่ใช้ (แขวง|ตำบล|ต\.)([ก-๙]+) เพราะเดี๋ยวมันจะกลายเป็น 2 cell ครับผม

เขต/อำเภอ + จังหวัด #

จากการสังเกตมันจะมาด้วยกันตลอด ผมเลยมันรวมเพื่อความรวดเร็วและง่าย เพราะจังหวัดนี่มันไม่มีอะไรบอกเราเลยครับว่าขอบเขตของมันคืออะไรนอกจากอำเภอ ถ้าปล่อยลอยมันอาจจะไปติดพวก ห้อง หรือ ถนน มาแทนก็ได้

REGEXEXTRACT(A3:A,"(เขต[ก-๙]+|อ\.[ก-๙]+)\s?จ?\.?([ก-๙]+)?")

ครบ 4 ช่องที่ต้องการแล้วครับ ใครจะเพิ่ม “บ้านเลขที่” หรือ “ถนน” ก็ลองดูได้ครับ แต่อาจจะต้องเพิ่มท่าพิเศษเข้าไปอีกได้

อย่าลืม ARRAYFORMULA() #

ทำได้ทุกช่องแล้วก็จัดยัดใส่ =ARRAYFORMULA() กันได้เลยนะครับ จะได้ไม่ต้องลากสูตรกันยาวๆ เพื่อไม่ให้ error ก็ =IFERROR() ครอบอีกทีก็ดีนะครับ

=IFERROR(
    ARRAYFORMULA(
        REGEXEXTRACT(A3:A,"\d{5}$")
    ),
    ""
)

ตัวอย่างตาราง #

Address Spliter

ใครมีคำถามหรือสงสัยในจุดไหนสามารถ inbox มาถามผมได้ที่ Facebook Page นะครับ คำถามไหนน่าสนใจเดี๋ยวผมเอามาเขียน blog แบ่งปันกันอีกแน่นอนครับ