RSS

เทคนิคการตัดข้อความที่คั่นด้วยเครื่องหมายต่าง ๆ

03 Apr

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

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

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

ExtractText

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

  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 เพื่อจะใช้เป็นจุดเริ่มตัดข้อความ
 
2 Comments

Posted by on 03/04/2011 in Mid, Rept, Rows, Substitute, Trim

 

2 responses to “เทคนิคการตัดข้อความที่คั่นด้วยเครื่องหมายต่าง ๆ

  1. Supuk

    28/10/2013 at 3:27 pm

    มีข้อสงสัยในเรื่องการตัดชื่อกับนามสกุลแยกออกจากกันค่ะ
    ถ้าหากผู้พิมพ์ เคาะ space bar 2 ครั้ง จากสูตร =RIGHT(A3,LEN(A3)-FIND(” “,A3)) ควจจะแก้ไขสูตรอย่างไรค่ะ รบกวนขอคำแนะนำด้วยนะคะ
    สุพักดิ์

     
  2. Supuk

    28/10/2013 at 3:32 pm

    ขอโทษค่ะ โพสผิดที่

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: