หน้าเว็บ

วันพุธที่ 9 ธันวาคม พ.ศ. 2552

การตัดข้อความกรณีที่คำนำหน้านามติดกับชื่อ

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

ตามตัวอย่างนี้จะแสดงวิธีการหนึ่งที่จะแยกคำนำหน้านาม ชื่อ และนามสกุลออกจากกันในกรณีที่คำนำหน้านามมีความหลากหลาย ซึ่งจะใช้สูตร Array เข้ามาช่วยครับ

ภาพประกอบการตัดข้อความกรณีมีคำนำหน้านามติดกับชื่อ

NameSurename 
ซึ่งมีขั้นตอนดังนี้

  1. จากภาพให้ทำการกรอกคำนำหน้าชื่อที่คิดว่าจะมีทั้งหมดก่อนที่ F2:F12
  2. แล้วคีย์ในแต่ละเซลล์ดังนี้
    1. เซลล์ D3 คีย์สูตรเพื่อตัดนามสกุลมาแสดง
      =RIGHT(A3,LEN(A3)-FIND(" ",A3))
      Enter
    2. เซลล์ C3 คีย์สูตรเพื่อตัดชื่อมาแสดง
      =SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$2:$F$13,"")))),D3,"") Ctrl+Shift+Enter
    3. เซลล์ B3 คีย์สูตรเพื่อตัดคำนำหน้านามมาแสดง
      =SUBSTITUTE(SUBSTITUTE(A3,C3,""),D3,"")
      Enter

ตัวอย่างวิธีคิดหลักๆ จากสูตรในเซล C3

  1. เปลี่ยนคำนำหน้านามให้เป็นค่าว่าง ซึ่งจะเหลือเพียงชื่อและนามสกุล แล้วนับอักขระ จะได้จำนวนอักขระไม่รวมคำนำหน้านาม คำไหนที่ถูกเปลี่ยนโดยเอาคำนำหน้านามออกไปแล้ว จำำนวนอักขระจะน้อยลง เอาค่าที่น้อยที่สุดไปกระทำในข้อที่ 2 (ดูตัวอย่า่งการแกะสูตรด้านล่างประกอบ)
  2. ใช้สูตร Right() เพื่อตัดอักขระด้านขวาของเซลต้นแหล่งให้เหลือเท่ากับจำนวนที่นับได้ในข้อ 1 ผลลัพธ์จะได้ชื่อและนามสกุล
  3. ใช้สูตร Substitute() เพื่อเปลี่ยนนามสกุล(ซึ่งหาไว้ก่อนแล้วซึ่งก็คือเซล D3)ให้เป็นค่าว่างโดยต้องการให้เหลือแค่ชื่อ

ตัวอย่างการแกะสูตร จากสูตรที่ C3 ซึ่งเป็นสูตร Array

จากสูตร =SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$2:$F$12,"")))),D3,"")

  1. เปลี่ยนคำนำหน้านามให้เป็นค่าว่าง
    คลุม SUBSTITUTE(A3,$F$2:$F$12,"") แล้วกดแป้น F9 จะได้
    =SUBSTITUTE(RIGHT(A3,MIN(LEN({"นางสาวมานี สวยสม";"สาวมานี สวยสม";"มานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม"}))),D3,"")
  2. นับอักขระที่เหลือจากการเปลี่ยนคำนำหน้าชื่อให้เป็นค่าว่างแล้ว
    คลุม LEN({"นางสาวมานี สวยสม";"สาวมานี สวยสม";"มานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม";"นางสาวมานี สวยสม"}) แล้วกดแป้น F9 จะได้
    =SUBSTITUTE(RIGHT(A3,MIN({16;13;10;16;16;16;16;16;16;16;16})),D3,"")
  3. หาค่าที่น้อยที่สุดหลังจากนับแล้ว
    คลุม MIN({16;13;10;16;16;16;16;16;16;16;16}) แล้วกดแป้น F9 จะได้
    =SUBSTITUTE(RIGHT(A3,10),D3,"")
    คำตอบคือ "มานี "
    จะเห็นได้ว่าคำว่า "มานี " มีวรรคต่อท้ายมา 1 อักขระ ซึ่งสามารถทีจะใช้ฟังก์ชั่น Trim ครอบสูตรที่ C3 และ B3 เพื่อที่จะตัดช่องว่างนี้ออกไปได้ครับ ซึ่งจะได้สูตรเป็น
    1. เซลล์ C3
      =TRIM(SUBSTITUTE(RIGHT(A3,MIN(LEN(SUBSTITUTE(A3,$F$3:$F$13,"")))),D3,""))
      Ctrl+Shift+Enter
    2. เซลล์ B3
      =TRIM(SUBSTITUTE(SUBSTITUTE(A3,C3,""),D3,""))
      Enter

5 ความคิดเห็น:

คนกวาดถนนธรรมดาๆ กล่าวว่า...

ขอบคุณครับ..

คนกวาดถนนธรรมดาๆ กล่าวว่า...
ความคิดเห็นนี้ถูกผู้เขียนลบ
ไม่ระบุชื่อ กล่าวว่า...

เป็นประโยชน์อย่างมากครับ
ขอบคุณครับ

Unknown กล่าวว่า...

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

snasui กล่าวว่า...

แนบไฟล์ที่ลองทำเองแล้วที่ http://www.snasui.com จะช่วยดูให้ครับ