Microsoft Excel Tips and Tricks: การตัดข้อความกรณีที่คำนำหน้านามติดกับชื่อ

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

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

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

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

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