อยากแยกที่อยู่ อำเภอ จังหวัด ออกจากกัน ทำไงดี?

ถ้าเงื่อนไขไม่ซับซ้อน ใช้สูตรของโพสต์วันที่ 3 กุมภาพันธ์ 2023 ได้เลย

สูตรนั้นง่ายและสั้น 😊

แต่ถ้าเงื่อนไขซับซ้อนล่ะ?

เช่น บางครั้งเขียนคำว่า เขต ติดกับชื่อเขต เช่น เขตสวนหลวง แต่บางครั้งก็เว้นวรรคเป็น เขต สวนหลวง

บางครั้งไม่ระบุคำว่า เขต นำหน้า เช่น ระบุแค่ บางกะปิ แทนที่จะเป็น เขตบางกะปิ

บางครั้งไม่ระบุคำว่า แขวง นำหน้า เช่น ระบุแค่ หัวหมาก แทนที่จะเป็น แขวงหัวหมาก

ต้องการชื่ออำเภอเมืองว่า เมือง (โดยไม่มีชื่อจังหวัดต่อท้าย) แต่ในข้อมูลมีชื่อจังหวัดต่อท้าย เช่น อ.เมืองระยอง แถมชื่อบางอำเภอก็มีชื่อจังหวัด เช่น อำเภอบ้านตาก

ทุกอำเภอที่ขึ้นต้นด้วยคำว่าเมือง อาจไม่ใช่อำเภอเมือง เพราะมีอำเภอนึงไม่ใช่ นั่นคือ อำเภอเมืองยาง

ชื่อบางตำบลมีคำว่า เขต เช่น ตำบลคีรีเขต ตำบลหลักเขต

ชื่อบางอำเภอมีคำว่า เขต เช่น อำเภอสนามชัยเขต

บางครั้งระบุคำว่า จังหวัด นำหน้า เช่น จังหวัดฉะเชิงเทรา แต่บางครั้งเขียนแค่ชื่อจังหวัด เช่น ฉะเชิงเทรา

ต้องการชื่อจังหวัดว่า กรุงเทพฯ แต่ในข้อมูลมีทั้ง กทม. กรุงเทพ กรุงเทพมหานคร

ใช่, เคสเยอะและซับซ้อนมาก

เคสซับซ้อน สูตรก็ต้องซับซ้อนตาม

และนี่คือสูตร Excel 365 ใช้แยกที่อยู่ ตำบล อำเภอ จังหวัด รหัสไปรษณีย์ (จากภาพคือสูตรในเซลล์ A11) 😊

=LET(

Address,SUBSTITUTE(TRIM(A2:A7),UNICHAR(160),” “),

AddWOPrefix,MAP(Address,LAMBDA(a,TRIM(TEXTJOIN(” “,,TEXTSPLIT(a,{“ตำบล”,”ต.”,”แขวง”,”อำเภอ”,”อ.”,”จ.”,”จังหวัด”}))))),

Street,TEXTBEFORE(Address,{“แขวง”,”ตำบล”,”ต.”},,,,TEXTBEFORE(AddWOPrefix,” “,-4)),

Tambol,TEXTBEFORE(TRIM(TEXTAFTER(Address,{“แขวง”,”ตำบล”,”ต.”},,,,TEXTAFTER(AddWOPrefix,” “,-4))),” “),

PreProvince,TEXTBEFORE(TEXTAFTER(AddWOPrefix,” “,-2),” “),

Province,MAP(PreProvince,LAMBDA(m,IF(COUNT(FIND({“กรุงเทพ”,”กทม”},m)),”กรุงเทพฯ”,m))),

PreAmphur,TEXTBEFORE(TEXTAFTER(AddWOPrefix,” “,-3),” “),

Amphur,MID(PreAmphur,IF(LEFT(PreAmphur,3)=”เขต”,4,1),IF(FIND(Province,PreAmphur&Province)=6,5,99)),

PostCode,–TEXTAFTER(Address,” “,-1),

HSTACK(Street,Tambol,Amphur,Province,PostCode))

ใช่, สูตรโหดมาก!

แต่แก้ปัญหาที่เขียนมาได้ทั้งหมด

แถมเขียนสูตรเดียว แยกได้ 5 คอลัมน์เลยนะ 😊

( จากสูตร ข้อมูลที่อยู่คือ A2:A7 ถ้านำไปใช้งาน ให้เปลี่ยนช่วงข้อมูลให้สอดคล้องกับข้อมูลที่มี เช่น เปลี่ยนเป็น A2:A1000 )

เทคนิคบางส่วนของสูตรนี้ได้รับคำแนะนำจากคุณ Excel Wizard

ใช่ครับ, เราได้พูดคุยแลกเปลี่ยนความคิดเห็นกัน และเขาก็แชร์มุมมองว่าปรับสูตรให้สั้นลงได้ (เดิมยาวกว่านี้อี๊ก)

สูตรนี้ใช้ได้เฉพาะ Excel 365 เท่านั้นนะครับ

ลองมองสูตรนี้เป็นส่วนหนึ่งของการประยุกต์ใช้ฟังก์ชันใหม่ของ Excel 365 เช่น LET, LAMBDA, MAP

อยากให้พวกเราฝึกใช้กัน มันเจ๋งและทรงพลังมากจริง ๆ

คิดเห็นยังไงกับสูตรนี้ บอกเล่าเก้าสิบกันหน่อยนะครับ 😊