ดึงรูปแบบด้วย REGEXEXTRACT

REGEXMATCH บอกคุณว่ามีรูปแบบอยู่หรือไม่ บ่อยครั้งคุณต้องการข้อความที่ตรงกันเอง เช่น ตัวเลขจาก Order #4821 หรือโดเมนจากอีเมล

นั่นคือหน้าที่ของ REGEXEXTRACT ฟังก์ชันนี้ค้นหาส่วนแรกของข้อความที่ตรงกับรูปแบบ แล้วส่งคืนส่วนนั้นเป็นค่าของเซลล์

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

REGEXEXTRACT(text, pattern) ส่งคืนสตริงย่อยแรกของ ข้อความ ที่ตรงกับ รูปแบบ

หากไม่พบสิ่งที่ตรงกัน ฟังก์ชันจะส่งคืนข้อผิดพลาด #N/A แทนที่จะเป็นค่าว่าง ซึ่งเป็นสิ่งสำคัญที่ต้องจำไว้

สูตรด้านล่างดึงชุดตัวเลขแรกออกจาก A2 สำหรับ Order 4821 สูตรจะส่งคืน 4821

REGEXEXTRACT จะส่งคืนข้อความเสมอ แม้ว่าอักขระที่ตรงกันจะเป็นตัวเลขก็ตาม ดังนั้น 4821 ที่ได้กลับมาจึงเป็นสตริงข้อความ ไม่ใช่ตัวเลขที่คุณนำไปบวกได้

หากต้องการแปลงเป็นตัวเลขจริง ให้ครอบด้วย VALUE สูตรด้านล่างจะดึงตัวเลขแล้วแปลงค่าเพื่อให้คุณนำผลลัพธ์ไปคำนวณทางคณิตศาสตร์ได้

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

สูตรด้านล่างดึงตัวอักษรนำหน้าจากรหัสอย่าง INV2024 และส่งคืนค่า INV [A-Z]+ หมายถึงตัวอักษรพิมพ์ใหญ่หนึ่งตัวขึ้นไปที่อยู่ต่อเนื่องกัน

สมมติว่า A2 มีค่า SKU-4821-RED และคุณต้องการเฉพาะตัวเลขตรงกลาง คุณสามารถระบุตำแหน่งด้วยบริบทที่อยู่รอบๆ ภายในรูปแบบได้

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

โปรดสังเกตวงเล็บในสูตรล่าสุด: (\d+) วงเล็บเหล่านี้เรียกว่า กลุ่มการจับ

เมื่อรูปแบบมี กลุ่มการจับ REGEXEXTRACT จะคืนค่าเฉพาะสิ่งที่อยู่ภายในวงเล็บ ไม่ใช่ข้อความที่จับคู่ได้ทั้งหมด ดังนั้น -(\d+)- จึงคืนค่า 4821 และตัดเครื่องหมายขีดออก

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

จาก sam@coddykit.com คุณต้องการโดเมน coddykit.com ให้จับคู่ @ แล้วจับทุกอย่างหลังจากนั้นจนถึงท้ายสตริง

สูตรด้านล่างจะคืนค่าส่วนของ A2 ที่อยู่หลังเครื่องหมาย @ โดย (.+) จะจับอักขระใด ๆ อย่างน้อยหนึ่งตัว และ $ จะกำหนดให้การจับคู่นั้นอยู่จนถึงท้ายสตริง

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

สูตรด้านล่างจะจับอักขระที่อยู่ต้นข้อความของ A2 ซึ่งไม่ใช่ช่องว่าง สำหรับ John Smith สูตรจะคืนค่า John โดย [^ ]+ หมายถึงอักขระอย่างน้อยหนึ่งตัวที่ไม่ใช่ช่องว่าง

เนื่องจากการดึงข้อมูลที่ไม่สำเร็จจะแสดง #N/A การลากสูตรลงมาตามคอลัมน์ที่มีข้อมูลปะปนกันอาจทำให้แผ่นงานเต็มไปด้วยข้อผิดพลาด

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

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

สูตรด้านล่างจะดึงคำสถานะที่ปรากฏใน A2 ไม่ว่าจะเป็น OPEN, CLOSED หรือ HOLD โดยจะคืนค่ารายการแรกที่พบ

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

ลองนึกภาพว่าคอลัมน์ A มีสตริงคำสั่งซื้อ เช่น Order 4821 - shipped สูตรด้านล่างเมื่อลากลงมาตามคอลัมน์ B จะดึงเฉพาะหมายเลขคำสั่งซื้อจากทุกแถว ทำให้ได้คอลัมน์ที่ดูเหมือนเป็นตัวเลขอย่างเป็นระเบียบจากข้อความต้นทางที่ยุ่งเหยิง

คุณได้เรียนรู้ว่า REGEXEXTRACT(text, pattern) จะคืนค่าสตริงย่อยแรกที่ตรงกันใน Google ชีต ประเด็นสำคัญมีดังนี้:

• ผลลัพธ์เป็น ข้อความ เสมอ ให้ครอบด้วย VALUE หากต้องการใช้เป็นตัวเลข
• กลุ่มการจับในวงเล็บจะคืนค่าเฉพาะส่วนที่อยู่ด้านใน
• หากไม่พบการจับคู่จะได้ #N/A จึงควรป้องกันด้วย IFERROR
• | ช่วยให้รูปแบบเดียวจับคู่ทางเลือกได้หลายแบบ

ต่อไปคุณจะก้าวพ้นการอ่านข้อความ และเริ่มเขียนข้อความใหม่โดยตรงด้วย REGEXREPLACE