สูตร รวม คำนำ หน้าชื่อ นามสกุล excel

ผมเชื่อว่าเวลาที่เพื่อนๆ เจอข้อมูลที่มีทั้งคำนำหน้าชื่อและชื่อรวมอยู่ในช่องเดียวกัน แล้วต้องการแยกคํานําหน้าชื่อเป็นคนละช่องด้วย Excel จะเจอปัญหาถ้าคำนำหน้านั้นเขียนติดกับชื่อโดยไม่มีเว้นวรรคเลย!!

แต่ไม่ต้องห่วง คลิปนี้จะสอนคุณเขียนสูตรเพื่อแก้ปัญหาดังกล่าวเอง มาดูกันเลยว่าทำยังไง

แชร์ความรู้ให้เพื่อนๆ ของคุณ

แยกชื่อ นามสกุล จากที่อยู่ในคอลัมน์เดียว ให้ออกมาเป็น 3 คอลัมน์ คือ -คำนำหน้านาม -ชื่อ -นามสกุล โดยใช้ฟังก์ชันที่อยู่ใน excel

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

ในการทำงานจริง DataRevol นำ Text File เข้า Excel โดยใช้ Power Query หรือการ get data ซึ่งแยกนามสกุลในขั้นตอนนี้ได้เลย (โดยใช้ power query ลองอ่านบล็อกเก่าเรื่อง แยกข้อความใน excel นะจ๊ะ) แต่จะลองนำมาเป็นตัวอย่าง โดยเอาเข้า excel ทั้งหมดอยู่ในคอลัมน์เดียวกัน

หรือ เราจะใช้ Flash Fill ก็สะดวกดี

เนื่องจาก ชื่อบุคคล ผู้มาขอรับบริการ อาจจะเป็นเรื่องข้อมูลส่วนบุคคล ก็เลยไปเอาชื่อของ บุคคลสาธารณะ มาใช้เป็นตัวอย่างก็แล้วกันเนาะ ลักษณะจะคล้าย ๆ กับตัวอย่างนี้ คือ มีคำนำหน้าชื่อที่หลากหลาย มีชื่อ นามสกุล

• แยกชื่อ นามสกุล โดยใช้ Text to Column ผสมสูตร
  • แยกนามสกุลด้วย text to column
    • ปัญหาที่ไม่คิดว่าจะพบ
    • ทำความสะอาดข้อมูล
  • แยกคำนำหน้านาม กับ ชื่อ
    • แยกชื่อออกมา
    • แยกคำนำหน้านาม
• ใช้สูตรแยก
  • แยกนามสกุล
  • แยกชื่อ
    • ตัดนามสกุลออก
    • เอาคำนำหน้านามออก
  • แยกคำนำหน้า
  • สรุปสูตรแยกชื่อ นามสกุล

แยกชื่อ นามสกุล โดยใช้ Text to Column ผสมสูตร

ความจริง ถ้าตอนพิมพ์เข้าไป แบ่งแต่ละอย่างด้วยวรรค ก็จะง่ายขึ้นมาทันที คือเราสามารถใช้ Text to Column หรือ Flashfill แยกชื่อ นามสกุล ได้เลย

อย่างตัวอย่างนี้ เราใช้ Text to Column แยกนามสกุลออกมาก่อนได้เลย แต่นี่มีคำนำหน้านาม ซึ่งไม่เหมือนกันเสียอีก คือถ้าเป็น นาย นาง อันนี้จะรู้ว่ามี 3 อักขระ ใช้ len ช่วยแยกได้ แต่นี่มีทั้ง นาย นาง นางสาว หม่อมหลวง คุณหญิง ฯลฯ ซึ่งมีจำนวนที่ไม่แน่นอน ไม่มีอะไรให้ยึดเลยสักอย่าง

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

แยกนามสกุลด้วย text to column

ขั้นแรกนี้ เราใช้ Text to Column แยก นามสกุล ออกมาก่อน เพราะมันง่ายดี แต่สุดท้าย ตอนที่แยก คำนำหน้านาม กับ ชื่อ ก็ต้องใช้สูตรช่วยอีกที

เราจะแยก ชื่อ นามสกุล โดยใช้ Text To Column ง่าย ๆ นี่แหละจ้ะ

ปัญหาที่ไม่คิดว่าจะพบ

ตอนแรก ใช้ Text To Column โดยเลือกใช้ delimiter เป็น space แล้วคิดว่าน่าจะแยก หมายเหตุหลังนามสกุล (ที่อยู่ในวงเล็บ) ออกมาด้วย ปรากฏว่า เจอเรื่องประหลาดใจ บางชื่อยังคงอยู่ในคอลัมน์เดียวกันนามสกุล อ้าว เอ้ย ทำไมว้า… ต้นฉบับบันทึกมาอย่างไรกันแน่

ตรวจสอบแล้ว ผลออกมาว่า สิ่งที่คั่นระหว่าง ชื่อ กับ นามสกุล บางอัน มันไม่ใช่ space แต่เป็น nonbreaking space

ถ้าจะถามว่า datarevol รู้ได้อย่างไร ตอบแบบกำปั้นทุบดินเลยว่า ตอนที่ทำเรื่องนี้จริง ๆ พอเจอปัญหานี้ ก็ใช้วิธี copy ข้อมูลที่มีปัญหาไปดูใน word โดยให้มันโชว์ paragraph marks (ที่เป็นเครื่องหมาย pilcrow อยู่ในกลุ่ม paragraph) จึงเห็นว่าเป็น nonbreaking space

ด้วยเหตุผลนี้ การใช้ Text To Column โดยเลือกใช้ delimiter เป็น space มันจึงใช้ไม่ได้ผล

ทำความสะอาดข้อมูล

ต้องกลับมาตั้งต้นที่ข้อมูลเดิมใหม่ โดยเราจะเปลี่ยนจาก nonbreaking space ให้เป็น space โดยเลือกคอลัมน์ที่จะเปลี่ยน กด ctrl + H เพื่อเลือก Find and Replace พอขึ้นมา ตรงค้นหา ให้กดคีย์บอร์ด Alt+0160 (สำหรับ character code ของ nonbreaking space คือ 160 ) ซึ่งเราจะเปลี่ยนให้เป็น Space (ด้วยการเคาะ space bar 1 ที)

กลับมาใช้ Text to Colums ก็แยกนามสกุลออกมาได้แล้ว  เราก็จะได้ สองคอลัมน์ เป็นคำนำหน้าชื่อพร้อมชื่อ กับ นามสกุล

แยกคำนำหน้านาม กับ ชื่อ

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

อันนี้สารภาพตรง ๆ ว่า ขณะที่เขียนนี่ หาวิธีที่ดีกว่าการสร้างรายการคำนำหน้าชื่อออกมาไม่ได้

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

ซึ่งจากตัวอย่าง เราจะได้เป็น $A$2:$A$7 (ซึ่งตรงนี้จะเป็นอาร์เรย์ นะจ๊ะ)

แยกชื่อออกมา

ในตัวอย่างนี้ คำนำหน้านามกับชื่อ (ได้มาจาก Text to Columns ในหัวข้อก่อนหน้า) จะอยู่คอลัมน์ C และ นามสกุล (ได้มาจาก Text to Columns) จะอยู่ที่คอลัมน์ F

เริ่มแรก เราจะแยกเอาชื่อมาก่อน เราใช้สูตร

=RIGHT(C2,MIN(LEN(SUBSTITUTE(C2,$A$2:$A$7,""))))

หลักการก็คือ เราเอา คำนำหน้านามและชื่อ ในคอลัมน์ C เป็นตัวตั้ง ใช้ฟังก์ชัน Right (เพราะจะดึงจากทางขวา แล้วนับจำนวนที่จะเอาโดยให้ดูจากลิสต์คำนำหน้าที่สร้างไว้

โปรดอ่านเรื่องการใช้ฟังก์ชันแต่ละชนิด ได้ที่เรื่อง แยกตัวเลขจากข้อความ

สำหรับแนวคิดและการอธิบายขั้นตอนการใช้ฟังก์ชันในสูตรนี้ โปรดเลื่อนไปอ่านด้านล่าง

แยกคำนำหน้านาม

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

=SUBSTITUTE(C2,E2,"")

เท่านี้เองจ้ะ

ใช้สูตรแยก

เอาใหม่ เพื่อให้ดูเป็นมืออาชีพ เรามาใช้เขียนสูตรใช้ฟังก์ชัน แยกชื่อ นามสกุล กันดีกว่า แนะนำให้ย้อนกลับไปดูคำอธิบายสูตรในเรื่อง แยกตัวเลขจากตัวอักษร ก่อนนะจ๊ะ เพราะว่าแนวคิดเหมือนเดิมเป๊ะ ๆ

ให้ ต้นฉบับ อยู่ที่คอลัมน์ B

สมมติว่า B2 มีข้อมูลว่า “หม่อมหลวงอภิมงคล โสณกุล”  

แยกนามสกุล

ก่อนอื่น แยกนามสกุลมาก่อน เพราะว่านี่คือขั้นตอนที่ง่ายที่สุดแล้ว มี space หรือ ช่องว่าง (“ “) เป็นตัวคั่นให้จับทิศทางได้ ตรงนี้ไม่มีอะไรยุ่งยาก ใช้สูตร Right เพื่อตัดข้อความด้านขวา

สมมติว่า ชื่อ นามสกุล อยู่ที่ B2 เราใช้สูตรแยกนามสกุล โดยใช้ช่องว่าง ดังนี้

=RIGHT(B2,LEN(B2)-FIND(" ",B2))

เพียงเท่านี้ เราก็จะได้นามสกุล “โสณกุล” แยกมาแล้ว

แยกชื่อ

ตรงนี้จะยากกว่าการแยกนามสกุลหน่อย แต่เราลองมาคิดแบบนี้ ว่าเราจะทำอย่างไรบ้าง จึงจะ แยกชื่อ นามสกุล และคำนำหน้านามออกจากกัน ตรงนามสกุลเราตัดไปได้ เพราะทำผ่านไปแล้ว แต่ คำนำหน้านาม กับ ชื่อ นี่ล่ะ?

• สิ่งที่เรามีในตอนนี้ก็คือ เราได้ นามสกุล ซึ่งมีตัวคั่นชัดเจนคือ space หรือ ช่องว่าง
• เรามีคำนำหน้านาม ซึ่งไม่สามารถใช้อะไรระบุได้ นอกจากพิมพ์เข้าไปตรง ๆ ทำเป็นลิสต์เอาไว้ ซึ่ง ลิสต์รายการนี้ จะเป็นอาร์เรย์ (อันนี้ต้องย้ำ เพราะ ถ้าเป็น Excel เวอร์ชันเก่า ๆ ท่านจะต้องกด Ctrl+Shift+Enter ด้วย
• การเอา ชื่อ เพียงอย่างเดียว ก็ต้องเอา ชื่อนามสกุลเต็ม มาลบด้วย นามสกุล และ คำนำหน้านาม

ก่อนอื่น จะทำลิสต์รายการ คำนำหน้าชื่อ ไว้ที่คอลัมน์ A ช่วง A2 – A7 ตรงนี้ ในการทำงานจริง จะเป็นปัญหาจุกจิกกวนใจที่สุด ลองนึกถึงการทำงานกับรายชื่อจำนวน 5 หมื่นกว่า ๆ มันมีคำนำหน้านามเยอะแยะมากมายกว่าตัวอย่างนนี้มาก และถ้าทำลิสต์ไว้ไม่หมด หมายถึง ขบวนการแยกจะให้ผลลัพธ์ผิดพลาด

ยกตัวอย่าง ชื่อ “หม่อมหลวงอภิมงคล โสณกุล” อยู่ใน Cell B2

ตัดนามสกุลออก

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

สิ่งที่เราจะทำก็คือ เอา “โสณกุล” ออกจากคำว่า  “หม่อมหลวงอภิมงคล โสณกุล” โดยเราจะใช้ฟังก์ชัน SUBSTITUTE เป็นตัวช่วย

มาแทนที่ด้วยสูตร SUBSTITUTE จะได้แบบนี้

=SUBSTITUTE("หม่อมหลวงอภิมงคล โสณกุล","โสณกุล","")

คือ ให้หาคำว่า “โสณกุล” ใน “หม่อมหลวงอภิมงคล โสณกุล” แล้วแทนที่ด้วย “” หรือ พูดง่าย ๆ ก็คือ ให้ลบ “โสณกุล” ออกจาก  “หม่อมหลวงอภิมงคล โสณกุล” ซึ่งเราจะได้สูตรแบบนี้

=SUBSTITUTE(B2,E2,"")

โดย B2 คือ ชื่อนามสกุลเต็ม E2 คือ นามสกุล ที่ได้จากสูตรในข้อ แยกนามสกุล

ผลลัพธ์ที่เราจะได้ในข้อนี้คือ “หม่อมหลวงอภิมงคล “ (ซึ่งจะมี Space ด้านหลังด้วย ระวังหน่อยนะ)

เอาคำนำหน้านามออก

สิ่งที่ทำต่อมา คือเอา “หม่อมหลวง” ออกจาก “หม่อมหลวงอภิมงคล “ ซึ่งจะใช้วิธีแทนที่เหมือนเดิม อย่างที่ทำตอน เอานามสกุลออก คือ

=SUBSTITUTE(“หม่อมหลวงอภิมงคล โสณกุล”,“หม่อมหลวง”,””)

แต่ปัญหาคือ เราไม่สามารถหา “หม่อมหลวง” ได้ตรง ๆ สิ่งที่ทำได้ก็คือ ลิสต์รายการ คำนำหน้านาม ต่าง ๆ ไว้ ซึ่งในตัวอย่างทำแค่ A2 ถึง A7 อย่างที่บอก ตรงนี้ยังเป็นปัญหาที่ยังแก้ไม่ตก ยังหาวิธีอื่นที่ดีกว่านี้ไม่ได้ เรายังคงใช้ SUBSTITUTE เหมือนเดิม

แต่ตรง A2 ถึง A7 ซึ่งเราจะใช้ $A$2:$A$7 (ใช้ $ เพื่อตรึงไม่ให้เลื่อน) นั้น เป็นอาร์เรย์ ซึ่ง ค่าตรงนี้ ถ้าเรา จะเอาไปใช้ โดยใช้

=SUBSTITUTE(B2,$A$2:$A$7,"")

ถ้าเราแสดงการทำงาน สิ่งที่อยู่ภายในคำสั่งนี้ ตรง $A$2:$A$7 เป็นอาร์เรย์ จะให้ผลลัพธ์ออกมาแบบนี้ เมื่อแทนค่า จาก A2 (หม่อมหลวง) A3 (นางสาว) ไปจนถึง A7 (นาวาอากาศเอก) ดังนั้น คำสั่งนี้ ก็เหมือนการทำ Substitute 6 ครั้ง เพื่อเอา สิ่งที่อยู่ใน A2 จนถึง A7 ออก ซึ่งใน B2 เป็น “หม่อมหลวงอภิมงคล โสณกุล”

ดังนั้น ชั้นตอนการ SUBSTITUE ตรง A2 จะทำงาน จาก  “หม่อมหลวงอภิมงคล โสณกุล” จะกลายเป็น “อภิมงคล โสณกุล” แต่ขั้นตอน SUBSTITUE ตรง A3 ถึง A7 จะไม่เข้าข่ายตามเงื่อนไข จึงส่งกลับผลลัพธ์ เดิมคือ หม่อมหลวงอภิมงคล โสณกุล” เพราะไม่ตัดอะไรออก

เขียนผลลัพธ์เป็นอาร์เรย์ ดังนี้

{“อภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”}

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

เมื่อรู้ว่าตัวที่เราจะเลือกมาใช้ จะเป็นตัวที่มีจำนวนอักขระน้อยที่สุด – เพราะตรงนี้ คือจำนวนอักขระ ที่เราจะเอาไปหักออกจากชื่อนามสกุลเต็ม ๆ เพื่อตัด คำนำหน้านาม – เราก็ใช้ MIN (แสดงค่าน้อยที่สุด) แต่ MIN ใช้กับ ตัวเลข (หรือ Number) ฟังก์ชันที่จะใช้นับก็คือ LEN

ถ้าเราใช้ LEN

= LEN(SUBSTITUTE(B2,$A$2:$A$7,""))

หรือ

LEN({“อภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”;”หม่อมหลวงอภิมงคล โสณกุล”})

จะได้ผลลัพธ์เป็น

{14;23;23;23;23;23}

ซึ่งเมื่อใช้ MIN เพื่อหาค่าน้อยที่สุด

MIN (LEN(SUBSTITUTE(B2,$A$2:$A$7,"")))

หรือ

MIN({14;23;23;23;23;23})

จะได้ผลลัพธ์เป็น 14 (ส่งกลับค่าที่น้อยที่สุด) 14 นี่ก็คือ “อภิมงคล โสณกุล” เราก็ใช้ RIGHT เพื่อดึงอักขระทางขวามา 14 ตัว

RIGHT(B2,14)

หรือ

= RIGHT(B2, MIN (LEN(SUBSTITUTE(B2,$A$2:$A$7,"")))

จะได้ผลลัพธ์เป็น “อภิมงคล โสณกุล”

เมื่อใช้

=SUBSTITUTE(“อภิมงคล โสณกุล”,E2,””)

E2 คือนามสกุล ที่เราหาได้จากหัวข้อ เอานามสกุลออก

=SUBSTITUTE(RIGHT(B2,MIN(LEN(SUBSTITUTE(B2,$A$2:$A$7,””)))),E2,””)

หรือ ถ้าอยากเขียนยาว ๆ ก็

=SUBSTITUTE(RIGHT(B2,MIN(LEN(SUBSTITUTE(B2,$A$2:$A$7,””)))),RIGHT(B2,LEN(B2)-FIND(” “,B2)),””)

แยกคำนำหน้า

อันนี้ไม่มีอะไรมาก เอาชื่อนามสกุลตั้ง แล้วเอา นามสกุล ลบ ชื่อ ที่ได้จากการหาในสองข้อก่อน ใช้สูตร

อันแรก

SUBSTITUTE(B2,D2,"")

B2 คือชื่อนามสกุลเต็ม D2 คือ ชื่อ ที่ได้จากข้อ แยกชื่อ

พอได้แล้ว เราก็มาเอา นามสกุล ที่ได้ จากข้อ แยกนามสกุล ก็จะได้

=SUBSTITUTE(SUBSTITUTE(B2,D2,""),E2,"")

ก็เพียงเท่านี้เอง สาธุชน

สรุปสูตรแยกชื่อ นามสกุล

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

สมมติให้ ชื่อ นามสกุล อยู่ในคอลัมน์ B

สร้างรายการ คำนำหน้านาม ไว้ที่คอลัมน A

ที่คอลัมน์ C แยกคำนำหน้านาม ใช้สูตร

=SUBSTITUTE(SUBSTITUTE(B2,D2,""),E2,"")

ที่คอลัมน์ D แยกชื่อ ใช้สูตร

=SUBSTITUTE(RIGHT(B2,MIN(LEN(SUBSTITUTE(B2,$A$2:$A$7,"")))),E2,"")

ที่คอลัมน์ E แยกนามสกุล ใช้สูตร

=RIGHT(B2,LEN(B2)-FIND(" ",B2))

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