สูตร Excel สำหรับแยกชื่อและนามสกุล ด้วยฟังก์ชัน TEXTBEFORE และ TEXTAFTER

ผมเคยเขียนวิธีการแยกชื่อ-สกุลออกมากันไปแล้วตอนหนึ่ง โดยใช้ Text to Columns ในแถบ Data

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

ซึ่งถ้าความต้องการเป็นแบบนั้น เราก็จะต้องให้สูตร หรือไม่ก็ฟังก์ชันแหละ โดยสำหรับผู้ใช้ Office 365 ตอนนี้ฟังก์ชัน TEXTBEFORE() กับ TEXTAFTER() น่าจะเปิดให้ใช้แล้ว

สำหรับผู้ที่ไม่มี 2 ฟังก์ชันดังกล่าว ก็สามารถใช้ LEFT() และ RIGHT() รวมกับ FIND() และ LEN() ได้

แต่ก่อนอื่นมาดูข้อมูลก่อน

จากภาพตารางข้อมูลด้านบน ช่องข้อมูลหลักของเราจะเป็นคอลัมภ์ A ซึ่งจะเป็นชื่อ-สกุล รวมอยู่ในเซลล์เดียวกัน แยกด้วย spacebar ส่วนคอลัมภ์ B จะเป็นชื่อที่เราจะใช้ TEXTBEFORE() แยกออกมา และคอลัมภ์ C จะใช้ TEXTAFTER()

การใช้งาน TEXTBEFORE()

โดยฟังก์ชันนี้ออกแบบมาเพื่อลดความซับซ้อนในการใช้ฟังก์ชัน LEFT() ร่วมกับ FIND() หรือ SEARCH() กับ LEN() จากสูตรเดิม ๆ ที่ใช้กันมา รวมไปถึงถ้าไม่พบกำหนดได้เลยว่า ให้แสดงข้อความว่าอะไร โดย syntax จะเป็นอย่างงี้

=TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found])

• text – ก็คือข้อความ หรือเซลล์ที่เราจะดึงข้อมูลออกมา
• delimiter – เป็นอักษร หรือข้อความที่เราจะบอกให้ฟังก์ชันหา และเอาข้อความที่อยู่ก่อนหน้านี้ออกมาให้เรา
• instance_num [ไม่ต้องใส่ก็ได้] – แต่ถ้าใช้หมายถึงจะเลือกจากการเจอ delimiter ครั้งที่เท่าไหร่ ปกติค่านี้เป็น 1 แต่ถ้าตัวเลขติดลบ เช่น -1 ฟังก์ชันจะค้นจากด้านหลังย้อนกลับมา
• match_mode [ไม่ต้องใส่ก็ได้] – ใช้กำหนดว่าเราต้องการแยกความแตกต่างระหว่างตัวเล็กกับตัวใหญ่หรือไม่ (ภาษาอังกฤษ) ปกติค่าเป็น 0 คือจะแยก a กับ A ว่าต่างกัน ถ้ากำหนดเป็น 1 จะไม่สนใจจะเป็น a หรือ A ฟังก์ชันจะหยุด
• match_end [ไม่ต้องใส่ก็ได้] – ใส่เพิ่มให้ delimiter เป็นตัวอักษรสุดท้ายไหม ซึ่งในทางปฏิบัติคือ ถ้าไม่พบ delimiter ผลลัพธ์จะเป็นข้อความที่ใส่ไปนั่นเอง ปกติค่าจะเป็น 0 คือ ไม่ต้องทำ ถ้าไม่พบผลลัพธ์จะเป็น #N/A หรือข้อความที่เรากำหนดเองในอาร์กิวเมนต์สุดท้าย ส่วนถ้ากำหนดเป็น 1 ผลลัพธ์ก็คือ ค่าที่อยู่ในเซลล์ หรือข้อความที่เราใส่เข้าไปในอาร์กิวเมนต์แรก
• if_not_found [ไม่ต้องใส่ก็ได้] – ใช้กำหนดข้อความที่จะให้แสดง หากไม่พบ delimiter ในค่าที่อยู่ในเซลล์ หรือข้อความที่เราใส่เข้าไปในอาร์กิวเมนต์แรก ปกติหากไม่พบผลลัพธ์จะเป็น #N/A ส่วนถ้าจะกำหนดก็คือ ใส่ข้อความของเราในเครื่องหมายคำพูด (double quote) เช่น “Not found” หรือ “ไม่พบ” เป็นต้น

คราวนี้เราจะมาดูวิธีการใช้งานแบบง่าย ๆ แบบข้อมูลที่เราเตรียมไว้ ดังภาพ

โดยสูตรที่เราใช้ใน B2 ก็คือ

=TEXTBEFORE(A2," ")

ก็จะเห็นว่า เราจะได้เฉพาะข้อความที่อยู่หน้า spacebar กลับมา

การใช้งาน TEXTAFTER()

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

โดยจะสังเกตว่าอาร์กิวเมนต์ต่าง ๆ จะเหมือนกับ TEXTBEFORE() ทุกอย่าง แต่จะได้ผลลัพธ์ที่ตรงกันข้ามคือ ได้ข้อความที่อยู่หลัง delimiter กลับมา

โดยสูตรที่ใช้ใน C2 ก็คือ

=TEXTAFTER(A2," ")

และเราจะได้เฉพาะข้อความที่อยู่หลัง spacebar กลับมา

การแยกชื่อด้วยฟังก์ชัน LEFT() ร่วมกับ FIND()

สำหรับผู้ที่ไม่มีฟังก์ชัน TEXTBEFORE() ในช่อง B2 เราจะใช้สูตร

=LEFT(A2,FIND(" ",A2)-1)

โดยฟังก์ชัน LEFT() จะดึงตัวอักขระเริ่มจากทางซ้าย (ซึ่งบางทีผมก็เรียกว่า “ข้อความ” นะ 😅) ตามจำนวนที่เราใส่ไว้ในอาร์กิวเมนต์ที่ 2 ซึ่งในที่นี้เราใช้ฟังก์ชัน FIND() ซึ่งจะบอกว่าไปเจออักขระ หรือข้อความที่เราใส่ในอาร์กิวเมนต์แรก ว่าเจอเป็นลำดับที่เท่าไหร่

และเหตุที่ต้องลบ 1 ออกจากค่าที่ FIND() ส่งกลับมา เพราะ FIND() จะส่งตำแหน่งที่เจอ (ในตัวอย่างนี้คือ spacebar) กลับมา อย่างใน B2 คือตำแหน่งที่ 4 แต่เราต้องการแค่ 3 อักขระ ก็คือ “ชยา”

ส่วนหากใครไม่ได้ลบออก 1 อักขระที่เราไม่เห็นเพราะมันเป็น spacebar แต่ถ้าใช้ฟังก์ชัน LEN() ซึ่งจะบอกว่าข้อความนั้นมี 4 อักขระ

⚠️ฟังก์ชัน FIND() จะเป็นสนใจความแตกต่างระหว่างตัวเล็กกับตัวใหญ่ในภาษาอังกฤษนะ ถ้าเราไม่สนใจให้ใช้ฟังก์ชัน SEARCH() แทน

การแยกนามสกุลด้วยฟังก์ชัน RIGHT() ร่วมกับ LEN() และ FIND()

ส่วนการแยกนามสกุล เราจะใช้สูตรนี้

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

โดยฟังก์ชัน RIGHT() จะดึงอักขระเริ่มจากด้านขวาของอาร์กิวเมนต์ที่เราใส่เข้าไป ตรงกันข้ามกับ LEFT()

ส่วนอาร์กิวเมนต์ที่ 2 เราจะใช้ฟังก์ชัน LEN() ซึ่งจะบอกว่า A2 มีอักขระทั้งหมดกี่ตัว และในตัวอย่างคือ 13 อักขระ แล้วมาหักลบออกด้วยค่าที่ฟังก์ชัน FIND() ไปเจอ spacebar มา ซึ่งก็คือ 4 จะเหลือ 9 อักขระ ดังนั้นผลลัพธ์ก็เลยเป็น “ภัชรปรีดา”

จะเห็นได้ชัดว่ากรณีแยกนามสกุลฟังก์ชัน TEXTAFTER() ช่วยทำให้การพิมพ์สูตรของเราดูง่ายขึ้นเยอะเลย