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

Cover image

พอดีวันนี้ผมไปเจอคนถามใน 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 แบ่งปันกันอีกแน่นอนครับ

บทความใกล้เคียง