ใช้ TEXTAFTER() และ TEXTEFORE() ใน Microsoft Excel

งานทั่วไปคือการแยกวิเคราะห์หรือค้นหาอักขระภายในสตริงที่ระบุในรูปแบบ Microsoft Excel. โชคดีที่ Excel มีฟังก์ชันสตริงมากมายที่จะช่วย แต่นิพจน์ที่ได้นั้นซับซ้อน Power Query ไม่ต้องการนิพจน์ที่ซับซ้อน และ Flash Fill นั้นยอดเยี่ยมสำหรับการค้นหารูปแบบ แต่การเปิดตัวฟังก์ชันข้อความใหม่สองฟังก์ชันใน Excel จะทำให้สิ่งต่างๆ ง่ายขึ้นไปอีก

ในบทช่วยสอนนี้ เราจะทบทวนฟังก์ชันข้อความใหม่สองฟังก์ชันสั้นๆ: TEXTAFTER() และ TEXTBEFOR() เราจะเน้นที่ TEXTAFTER() แต่ทุกสิ่งที่คุณเรียนรู้จะนำไปใช้กับ TEXTEFORE() นอกเหนือจากทิศทางของอักขระที่แยกออกมา

ดู: Google Workspace กับ Microsoft 365: การวิเคราะห์พร้อมกับรายการตรวจสอบ (TechRepublic Premium)

ฉันใช้ Microsoft 365 บน Windows 10 64 บิต Microsoft เปิดตัวฟังก์ชันเหล่านี้ในเดือนกันยายน ดังนั้นควรพร้อมใช้งานสำหรับสมาชิก Microsoft 365 ทั้งหมด รวมถึง Excel สำหรับเว็บ

คุณสามารถดาวน์โหลดไฟล์สาธิต Microsoft Excel สำหรับบทช่วยสอนนี้

หากต้องการตรวจสอบ ให้เปิดเวิร์กบุ๊ก Excel แล้วป้อน =ข้อความในเซลล์ การเติมข้อความอัตโนมัติควรแสดงฟังก์ชันการทำงานใหม่ดังที่แสดงใน รูป A. ถ้าคุณไม่เห็นพวกเขา และคุณแน่ใจว่าคุณมี Microsoft 365 โปรดติดต่อผู้ดูแลระบบของคุณ

รูป A

วิธีใช้ TEXTAFTER() ใน Excel

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

ฟังก์ชัน TEXTAFTER() ใหม่ของ Excel จะส่งคืนอักขระที่เกิดขึ้นหลังอักขระหรือสตริงที่ระบุ ทำให้อาการปวดหัวข้างต้นเป็นโมฆะ

TEXTAFTER() ใช้ไวยากรณ์ต่อไปนี้:

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

มีเพียงสองอาร์กิวเมนต์ที่ต้องการ:

• text นี่คือซีรีส์ที่คุณตามหา
• delimiter เธอเป็นตัวละครเฉพาะที่แยกตัวละครก่อนและหลัง

อาร์กิวเมนต์ที่เหลือเป็นทางเลือกและจะช่วยคุณปรับปรุงผลลัพธ์:

• instance_num ตัวคั่นหลังจากนั้นที่คุณต้องการแยกข้อความ โดยค่าเริ่มต้น, instance_num = 1. ตัวเลขติดลบเริ่มการค้นหาข้อความจากด้านขวา คุณจะใช้อาร์กิวเมนต์ที่เป็นตัวเลือกนี้เมื่อตัวคั่นปรากฏขึ้นมากกว่าหนึ่งครั้ง
• match_mode กำหนดว่าการค้นหาข้อความคำนึงถึงขนาดตัวพิมพ์หรือไม่ 0 เป็นค่าเริ่มต้นและคำนึงถึงขนาดตัวพิมพ์ 1 ไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
• match_end ประเมินส่วนท้ายของข้อความเป็นตัวคั่น 0 เป็นค่าเริ่มต้น และข้อความจะเหมือนกันทุกประการ 1 จะจับคู่ตัวคั่นที่ส่วนท้ายของข้อความ
• if_not_found ค่าที่ฟังก์ชันส่งคืนหากไม่พบรายการที่ตรงกัน #N/A คือการตั้งค่าเริ่มต้น

ตัวคั่นคืออักขระที่แยกอักขระในสตริง หากคุณไม่คุ้นเคยกับคำศัพท์นี้ คุณจะทำความคุ้นเคยกับมันในตัวอย่างแรกของเรา อย่างที่คุณเห็นใน รูป Bทำงานใน C3

=TEXTAFTER(B3,"/")

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

รูป B

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

รูป C ผลลัพธ์ของการใช้ฟังก์ชัน if_not_found มีดังนี้:

=TEXTAFTER(B3,"/",,,,"Missing delimiter")

ข้อความ “ไม่มีตัวคั่น” มีความเฉพาะเจาะจงและมีประโยชน์มากกว่าค่าความผิดพลาด

รูป C

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

รูป d โดยจะแสดงอาร์กิวเมนต์นี้ในการดำเนินการในชุดข้อมูลอื่น และยังแสดงค่าที่ไม่สอดคล้องกันอีกด้วย คอลัมน์ C แสดงผลการรัน Flash Fill เพื่อค้นหารูปแบบที่จะส่งคืนเฉพาะส่วนสุดท้ายสำหรับการเปรียบเทียบ คอลัมน์ D ใช้ TEXTAFTER():

=TEXTAFTER(B3," ",2,,,"Missing delimiter")

รูป d

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

คุณสามารถลองใช้ฟังก์ชัน:

=TEXTAFTER(B3," ",-1,,,"Missing delimiter")

ในคอลัมน์ E อย่างที่คุณเห็นใน รูป Eยังคงไม่ทำงานเช่น CEO; อย่างไรก็ตาม มันสร้างความประหลาดใจ: E6 ไม่ส่งคืนอะไรเลย

ในตอนแรก คุณอาจคิดว่าเหตุผลก็คือค่านั้นเป็นคำเดียว “Susan” และ “Harkins” ในเซลล์ถัดไปด้วย และฟังก์ชันจะส่งกลับ “Harkins” ตามที่คาดไว้

รูป E

หากคุณสงสัยว่ามีอักขระเว้นวรรคที่ส่วนท้ายของ Susan หรือ Harkins คุณพูดถูก หากคุณลบอักขระเว้นวรรคที่ส่วนท้ายของ susan ฟังก์ชันจะส่งคืนข้อความแสดงข้อผิดพลาด หากคุณพบสิ่งนี้ คุณสามารถตัดอาร์กิวเมนต์ข้อความในฟังก์ชัน TRIM() ได้:

=TEXTAFTER(TRIM(B3)," ",-1,,,"Missing delimiter")

วิธีใช้ TEXTBEFOR() ใน Excel

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

ไฟล์สาธิตที่ดาวน์โหลดได้มีข้อมูลเดียวกันกับ TEXTBEFOR() ในกรณีที่คุณต้องการดูการใช้งานจริง

ติดตามต่อไป

มีฟังก์ชันข้อความใหม่ TEXTSPLIT() ฉันจะกล่าวถึงสิ่งนี้ในบทความต่อ ๆ ไป