หากคุณต้องการแยกนามสกุลออกจากชื่อเต็ม คุณสามารถทำได้ด้วยสูตรที่ค่อนข้างซับซ้อนนี้ซึ่งใช้หลายฟังก์ชัน ในรูปแบบทั่วไปของสูตร (ด้านบน) ชื่อคือชื่อเต็ม โดยมีช่องว่างคั่นชื่อจากส่วนอื่นๆ ของชื่อ
ในตัวอย่าง เซลล์ที่ใช้งานอยู่มีสูตรนี้:
วิธีย่อใน excel
= RIGHT (name, LEN (name)- FIND ('*', SUBSTITUTE (name,' ','*', LEN (name)- LEN ( SUBSTITUTE (name,' ','')))))คำอธิบาย
ที่แกนกลาง สูตรนี้ใช้ฟังก์ชัน RIGHT เพื่อแยกอักขระที่เริ่มต้นจากด้านขวา ฟังก์ชันอื่นๆ ที่ประกอบเป็นส่วนที่ซับซ้อนของสูตรนี้ทำสิ่งเดียวเท่านั้น: ฟังก์ชันนี้จะคำนวณว่าต้องแยกอักขระกี่ตัว
ในระดับสูง สูตรจะแทนที่ช่องว่างสุดท้ายในชื่อด้วยเครื่องหมายดอกจัน '*' จากนั้นใช้ FIND เพื่อกำหนดตำแหน่งของดอกจันในชื่อ ตำแหน่งนี้ใช้คำนวณจำนวนอักขระที่จะแยกด้วย RIGHT
ฟังก์ชันจะแทนที่เฉพาะช่องว่างสุดท้ายอย่างไร นี่คือส่วนที่ฉลาด
หัวเข็มขัดคำอธิบายได้รับบิตเทคนิค
กุญแจสำคัญของสูตรนี้คือบิตนี้:
= RIGHT (B4, LEN (B4)- FIND ('*', SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))))
ซึ่งแทนที่จริงของช่องว่างสุดท้ายด้วย '*'
SUBSTITUTE มีอาร์กิวเมนต์ที่สี่ (เป็นทางเลือก) ซึ่งระบุว่าควรแทนที่ 'อินสแตนซ์' ของข้อความค้นหาใด หากไม่มีการระบุสิ่งใดสำหรับอาร์กิวเมนต์นี้ อินสแตนซ์ทั้งหมดจะถูกแทนที่ อย่างไรก็ตาม ถ้าสมมติว่ามีหมายเลข 2 มา ระบบจะแทนที่เฉพาะอินสแตนซ์ที่สองเท่านั้น ในตัวอย่างด้านบน อินสแตนซ์คำนวณโดยใช้ SUBSTITUTE ที่สอง:
SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))
ในที่นี้ ความยาวของชื่อที่ไม่มีช่องว่างจะถูกลบออกจากความยาวจริงของชื่อ หากมีเพียงช่องว่างเดียวในชื่อ ก็จะสร้าง 1 หากมีการเว้นวรรคสองช่อง ผลลัพธ์จะเป็น 2 เป็นต้น
ในชื่อตัวอย่างใน B4 มีช่องว่างสองช่องในชื่อ ดังนั้นเราจึงได้:
15 - 13 = 2
และใช้สองตัวในหมายเลขอินสแตนซ์:
LEN (B4)- LEN ( SUBSTITUTE (B4,' ',''))
ซึ่งแทนที่ช่องว่างที่สองด้วย '*' ชื่อจะมีลักษณะดังนี้:
'ซูซาน แอน*ช้าง'
ฟังก์ชัน FIND จะเข้ามาแทนที่ '*' ในชื่อ:
SUBSTITUTE (B4,' ','*',2)
ผลลัพธ์คือ 10 (* อยู่ในตำแหน่งที่ 10) ซึ่งถูกลบออกจากความยาวทั้งหมดของชื่อ:
FIND ('*', 'Susan Ann*Chang')
เนื่องจากชื่อมีอักขระ 15 ตัว เราจึงมี:
15-10 = 5
RIGHT ใช้หมายเลข 5 ดังนี้:
LEN (B4)-10
ซึ่งส่งผลให้ 'ช้าง'
วิธีเชื่อมต่อใน excel
อย่างที่คุณเห็น การคำนวณ 5 แบบง่ายๆ ข้างต้นเป็นงานหนักมาก!
การจัดการพื้นที่ที่ไม่สอดคล้องกัน
การเว้นวรรคเกินจะทำให้เกิดปัญหากับสูตรนี้ ทางออกหนึ่งคือการใช้ ฟังก์ชัน TRIM ขั้นแรกให้ทำความสะอาดสิ่งต่าง ๆ แล้วใช้สูตรการแยกวิเคราะห์
ผู้เขียน Dave Bruns