RSS

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

09 Dec

กรณีที่คำนำหน้านามไม่ติดกับชื่อ สามารถใช้ 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
 
7 Comments

Posted by on 09/12/2009 in Array, Find, Len, Min, Right, Split, Substitute, Trim

 

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

  1. กกกก

    04/05/2012 at 10:54 am

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

     
  2. กกกก

    04/05/2012 at 10:55 am

    ความคิดเห็นนี้ถูกผู้เขียนนำออก

     
  3. Anonymous

    16/08/2012 at 2:48 pm

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

     
  4. Witch Polly

    26/08/2012 at 1:09 pm

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

     
  5. Santipong Nasui

    26/08/2012 at 4:15 pm

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

     
  6. Supuk

    28/10/2013 at 3:32 pm

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

     
    • snasui

      31/10/2013 at 4:32 pm

      สามารถปรับเป็น =RIGHT(A3,LEN(A3)-FIND(” “,A3)-2) ครับ

       

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: