Microsoft Excel Tips and Tricks: เทคนิคการตัดข้อความที่คั่นด้วยเครื่องหมายต่าง ๆ

การตัดข้อความกรณีที่มีเครื่องหมายต่าง ๆ คั่นระหว่างข้อความ นอกจากจะใช้ Text to columns ได้แล้ว เรายังสามารถใช้สูตรมาจัดการได้ เพื่อจะได้ไม่ต้องทำ Text to columns ซ้ำ ๆ ครับ

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

ตัวอย่างตามภาพด้านล่าง ข้อความอยู่ที่เซลล์ A1 และคั่นด้วยเครื่องหมายคอมม่า (,) เราต้องการตัดข้อความออกเป็นแต่ละค่าโดยแสดงที่ A3 เป็นต้นไป

การแบ่งแต่ละข้อความเราสามารถใช้สูตรดังนี้ครับ

1. ที่ A2 คีย์
   =Len(A1)
   Enter
2. ที่ A3 คีย์สูตรเพื่อแบ่งข้อความ
   =TRIM(MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2))
   Enter > Copy ลงด้านล่าง

เราสามารถแกะสูตรในข้อ 2 ตามด้านล่างครับ

1. จากสูตรในข้อ 2 หมายถึงให้ตัดช่องว่างที่เกินความจำเป็นจากผลลัพธ์ของสูตร MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2)
2. จากสูตร MID(SUBSTITUTE(","&A$1,",",REPT(" ",A$2)),ROWS(A$3:A3)*A$2,A$2)) หมายถึงให้ตัดข้อความที่ได้จากผลลัพธ์ของสูตร SUBSTITUTE(","&A$1,",",REPT(" ",A$2)) โดยเริ่มจากตำแหน่งที่ได้จากผลลัพธ์ของสูตร ROWS(A$3:A3)*A$2 มาเป็นจำนวนอักขระตามค่าในเซลล์ A2
3. จากสูตร SUBSTITUTE(","&A$1,",",REPT(" ",A$2)) หมายถึงให้เปลี่ยนค่าอักขระ "," ในข้อความ ","&A$1 ให้เป็นอักขระ REPT(" ",A$2)
4. จากสูตร REPT(" ",A$2) หมายถึงให้แสดงค่า " " เป็นจำนวนครั้งเท่ากับค่าใน A2
5. จากสูตร ROWS(A$3:A3)*A$2 หมายถึงให้นำจำนวนแถวในช่วง A$3:A3 มาคูณกับค่าใน A2 เพื่อจะใช้เป็นจุดเริ่มตัดข้อความ