Snasui Excel Forum

รบกวนสอบถามคะ อยากจะแยกข้อมูล ที่อยู่ / แขวง / เขต / จังหวัด / รหัสไปรษณีย์ / เบอร์โทร ออกจากข้อมูลเดิมคะ (ตามเอกสารแนบ)

...

B2
=LEFT(A2,AGGREGATE(15,6,SEARCH({"ต.","แขวง","ตำบล"},$A2),1)-1)

C2
=LEFT(SUBSTITUTE(A2,B2,""),AGGREGATE(15,6,SEARCH({"อ.","อำเภอ","เขต"},SUBSTITUTE(A2,B2,"")),1)-1)

D2
=LEFT(SUBSTITUTE(A2,B2&C2,""),SEARCH("จ.",SUBSTITUTE(A2,B2&C2,""))-1)

E2
=LEFT(SUBSTITUTE(A2,B2&C2&D2,""),SEARCH(" ",SUBSTITUTE(A2,B2&C2&D2,"")))

F2
=LEFT(SUBSTITUTE(A2,B2&C2&D2&E2,""),5)

G2
=TRIM(SUBSTITUTE(MID(A2,SEARCH("โทร",A2),99),"โทรศัพท์",""))


Norkaz

แถมอีกตัวอย่างครับ

เซลล์ B2 คีย์

=IF(COLUMNS(B:$G)>5,LEFT($A2,FIND(C2,$A2)-1),SUBSTITUTE(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A2,",","|")," ","</s><s>")&"</s></t>","//s["&LEN($A2)-LEN(SUBSTITUTE($A2," ",""))+1-COLUMNS(B:$G)+1&"]"),"|",","),"โทรศัพท์",""))

Enter > Copy ไปด้านขวาและลงด้านล่าง

snasui wrote: Sat Jan 29, 2022 9:30 am แถมอีกตัวอย่างครับ

เซลล์ B2 คีย์

=IF(COLUMNS(B:$G)>5,LEFT($A2,FIND(C2,$A2)-1),SUBSTITUTE(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A2,",","|")," ","</s><s>")&"</s></t>","//s["&LEN($A2)-LEN(SUBSTITUTE($A2," ",""))+1-COLUMNS(B:$G)+1&"]"),"|",","),"โทรศัพท์",""))

Enter > Copy ไปด้านขวาและลงด้านล่าง

กลสูตรย้อนกลับ ใน FILTERXML แจ่มแมวเลยครับ

Norkaz

B2
=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:A18,"โทรศัพท์",)," ",REPT(" ",200)),(7-SEQUENCE(,6))*200+(SEQUENCE(,6,)=1)*10^4),((SEQUENCE(,6)=1)*(LEN(A2:A18)-LEN(SUBSTITUTE(A2:A18," ",))-5)+1)*200))

...

แถมอีกตัวอย่างครับ

B2

=IF(COLUMNS($B2:B2)=1,LEFT(A2,AGGREGATE(15,6,SEARCH({"ต.","แขวง","ตำบล"},$A2),1)-1),TRIM(SUBSTITUTE(MID(SUBSTITUTE(MID($A2,AGGREGATE(15,6,SEARCH({"ต.","แขวง","ตำบล"},$A2),1),999)," ",REPT(" ",99)),100*COLUMNS(B2:$C2)-99,99),"โทรศัพท์","")))

Copy ไปขวา และลงไป

Norkaz

แบบนี้ก็พอไหว

B2 =IF(B$1="ที่อยู่",LEFT($A2,FIND(C2,$A2)-1),TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"โทรศัพท์",""),IF(C2="",""," "&TRIM(CONCAT(" "&C2:$H2))),"")," ",REPT(" ",50)),50)))

รบกวนสอบถามเพิ่มเติมจ้า เนื้องจากข้อมูลบางตัวไม่ตรงกัน อยากจะสอบถามว่าข้อมูลมันจะดึงมาไหมคะ หรือสูตรจะขึ้นผิดไปเลย รบกวนพี่ๆดูข้อมูลนี้ทีจ้า อยากแยก ที่อยู่ ,แขวง/ตำบล , เขต/อำเภอ , จังวัด , รหัสไปรษณีย์ , เบอร์โทร (ตามเอกสารแนบคะ)

รบกวนสอบถามเพิ่มเติมจ้า เนื้องจากข้อมูลบางตัวไม่ตรงกัน อยากจะสอบถามว่าข้อมูลมันจะดึงมาไหมคะ หรือสูตรจะขึ้นผิดไปเลย รบกวนพี่ๆดูข้อมูลนี้ทีจ้า อยากแยก ที่อยู่ ,แขวง/ตำบล , เขต/อำเภอ , จังวัด , รหัสไปรษณีย์ , เบอร์โทร (ตามเอกสารแนบคะ)

สูตรที่เพื่อนสมาชิกตอบไปนั้นเพื่อแก้ปัญหากรณีข้อมูลมีครบตามหัวคอลัมน์ที่ให้มา หากมีไม่ครบก็ควรเติมให้ครบถึงจะไม่ต้องปรับสูตรครับ

เท่าที่สังเกตดูข้อมูลในไฟล์ล่าสุด ส่วนใหญ่สามารถคัดลอกมาไว้ที่คอลัมน์ที่อยู่ (คอลัมน์ F) ได้เลย

ไม่พบว่ามีการนำสูตรไปใช้ กรุณาใส่สูตรที่เพื่อนสมาชิกแนะนำไปแล้วมาด้วยพร้อมทั้งระบุเซลล์ที่ยังไม่ได้คำตอบ แจ้งมาด้วยว่าหากคำตอบถูกต้องควรได้ค่าแต่ละช่องเป็นแบบไหน จะได้ตอบต่อไปจากนั้นครับ

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

...

ตัวอย่างสูตร

F3

=IFERROR(IF(AND(COLUMNS($F3:F3)=1,ISERROR(AGGREGATE(15,6,SEARCH({"ต.","แขวง"},$E3),1))),$E3,IF(COLUMNS($F3:F3)=1,LEFT(E3,AGGREGATE(15,6,SEARCH({"ต.","แขวง"},$E3),1)-1),TRIM(SUBSTITUTE(MID(SUBSTITUTE(MID($E3,AGGREGATE(15,6,SEARCH({"ต.","แขวง"},$E3),1),999)," ",REPT(" ",99)),100*COLUMNS(F3:$G3)-99,99),"โทรศัพท์","")))),"")

Norkaz