Thep Excel
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| text | text | ข้อความต้นทางที่ต้องการแทนที่ สามารถเป็นสตริง หรือลิงก์ไปยังคอลัมน์ข้อความ |
| old_text | text | ข้อความเดิมที่ต้องการค้นหาและแทนที่ ต้องตรงกับตัวพิมพ์ (case-sensitive) ถ้าไม่ตรง SUBSTITUTE จะไม่ทำงาน |
| new_text | text | ข้อความใหม่ที่ใช้แทนที่ old_text สามารถเป็นสตริงว่าง (“”) เพื่อลบข้อความได้ |
| [instance_num]ไม่บังคับ | integer | ครั้งที่ต้องการแทนที่ (เช่น 1 = ครั้งแรก, 2 = ครั้งที่สอง) ถ้าไม่ระบุหรือเป็นค่าลบ จะแทนที่ทุกครั้งที่พบ |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
SUBSTITUTE แทนที่ข้อความที่ตรงกันในสตริง ทำงานโดยค้นหา old_text แล้วแทนที่ด้วย new_text โดยสนตัวพิมพ์ (case-sensitive) เหมาะมากเพราะไม่อิงตำแหน่ง ต่างจาก REPLACE ที่ใช้ตำแหน่งไบต์อย่างแน่นอน ยังสามารถระบุ instance_num เพื่อแทนที่เพียงครั้งที่ n ได้
ที่เจ๋งคือ SUBSTITUTE ใช้สำหรับสถานการณ์จริง เช่น เปลี่ยนชื่อเดิมเป็นชื่อใหม่ในข้อมูลขนาดใหญ่ ลบเครื่องหมายพิเศษ หรือทำความสะอาดรูปแบบโดยไม่ต้องรู้ตำแหน่งที่แน่นอน ถ้าต้องเลือกครั้งที่สองจากหลาย ๆ ครั้ง instance_num จะช่วยได้
ส่วนตัวผมชอบใช้ SUBSTITUTE กับ TRIM มากในการทำความสะอาดข้อมูลจาก Excel ก่อนวิเคราะห์ มีข้อสำคัญคือมันสนตัวพิมพ์ ดังนั้นถ้าข้อมูลสับสน ลองใช้ UPPER หรือ LOWER ก่อนแล้วค่อยเทียบ
How it works
เปลี่ยนคีย์เวิร์ดหรือแท็กที่ซ้ำ
แทนที่คำเก่าเป็นคำใหม่ในข้อความอธิบายสินค้าหรือบันทึก เพื่อให้ใช้คำมาตรฐาน
จัดรูปแบบข้อความที่ซ้ำหลายครั้ง
แทนที่คำหรือสัญลักษณ์เฉพาะครั้งที่ n เพื่อควบคุมเฉพาะบางตำแหน่ง เช่น เปลี่ยนตัวแบ่งครั้งที่สองเท่านั้น
ทำความสะอาดข้อมูลนำเข้า
ลบหรือแทนที่เครื่องหมายพิเศษจากข้อความนำเข้าก่อนนำไปคำนวณหรือแสดงผล
Examples
แทนที่ทุกครั้งที่พบVAR Product = "Samsung Galaxy S24 Samsung Galaxy Watch" RETURN SUBSTITUTE(Product, "Samsung", "Apple")
แทนที่คำว่า Samsung ทั้งหมดที่พบในสตริง (2 ครั้ง) ด้วย Apple เหมาะใช้เมื่อต้องเปลี่ยนแบรนด์หรือชื่อผลิตภัณฑ์ทั่ว ๆ ไป
แทนที่เฉพาะครั้งที่ระบุVAR Code = "INV-2024-001-Draft" RETURN SUBSTITUTE(Code, "-", "_", 2)
แทนที่เครื่องหมายลบ (-) เฉพาะครั้งที่ 2 เท่านั้น ส่วนครั้งอื่นยังคงเป็นเครื่องหมายลบ ใช้เมื่อต้องจัดการตำแหน่งเฉพาะในรูปแบบโค้ดหรือข้อมูลที่มีลักษณะแบ่งส่วน
ลบเครื่องหมายพิเศษจากข้อมูลVAR Phone = "(089)-123-4567" RETURN SUBSTITUTE(SUBSTITUTE(Phone, "(", ""), ")", "")
ลบวงเล็บทั้งหมดจากเบอร์โทรศัพท์โดยการซ้อน SUBSTITUTE เพื่อลบหลายอักขระ ใช้เมื่อต้องทำความสะอาดหรือเตรียมข้อมูลสำหรับการค้นหา
แทนที่สนตัวพิมพ์VAR Text = "Power BI power bi POWER BI" RETURN SUBSTITUTE(Text, "Power BI", "Tableau")
แทนที่เฉพาะ "Power BI" (ตัวพิมพ์ตามตัวอักษร) ไม่แทนที่ "power bi" หรือ "POWER BI" เพราะตัวพิมพ์ไม่ตรง ถ้าต้องแทนที่ไม่สนตัวพิมพ์ ให้ใช้ UPPER ทั้งสองข้างก่อนเปรียบเทียบ
FAQs
SUBSTITUTE กับ REPLACE ต่างกันอย่างไร?+
SUBSTITUTE แทนที่ตามเนื้อหาข้อความ ต้องค้นหา old_text ก่อน ส่วน REPLACE แทนที่ตามตำแหน่ง (ตัวอักษรลำดับที่เท่านั้น) ถ้าคุณรู้เนื้อหา ใช้ SUBSTITUTE; ถ้าคุณรู้ตำแหน่ง ใช้ REPLACE ผมแนะนำให้เลือกตามว่า ข้อมูลของคุณมี "ตำแหน่งคงที่" หรือ "เนื้อหาต่างกัน" ก่อน
SUBSTITUTE สนตัวพิมพ์หรือไม่?+
ใช่ SUBSTITUTE สนตัวพิมพ์อย่างเคร่งครัด (case-sensitive) ถ้า old_text = "Power" มันจะไม่แทนที่ "power" หรือ "POWER" ต้องตรงเนื้อหาและตัวพิมพ์ทุกตัว วิธีแก้ไข: ใช้ UPPER หรือ LOWER ทั้งสองข้างก่อน เช่น `SUBSTITUTE(UPPER(text), UPPER(old_text), new_text)` จากนั้นใช้ PROPER ปรับปรุงรูปแบบหลัง
instance_num = 0 หรือค่าลบจะเกิดอะไร?+
ถ้าใส่ instance_num = 0 หรือค่าลบ SUBSTITUTE จะแทนที่ทุกครั้ง (เหมือนไม่ระบุ) ใช้เวลาเป็น workaround ถ้าต้องการแทนที่ทั้งหมด แนะนำให้ไม่ระบุ instance_num สะอาดกว่า
ถ้า old_text ไม่พบในสตริงจะเกิดอะไร?+
SUBSTITUTE จะส่งคืนสตริงต้นทาง (text) โดยไม่มีการเปลี่ยนแปลง ไม่มี error ที่ส่งคืนมา ดังนั้นคุณไม่ต้องกังวลว่าหาไม่เจอจะพังระบบ มันยอดเยี่ยมสำหรับการจัดการข้อมูลที่ไม่แน่นอน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ SUBSTITUTE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
FIND คืนค่าตำแหน่งเริ่มต้นของ FindText ภายใน WithinText (เริ่มนับที่ 1) โดยค้นหาแบบแยกตัวพิมพ์ใหญ่/เล็ก และสามารถระบุ StartNum/NotFoundValue เพื่อควบคุมจุดเริ่มค้นหาและผลลัพธ์เมื่อไม่พบได้
LEN นับจำนวนอักขระของสตริง รวมช่องว่างด้วย ใช้สำหรับตรวจสอบความยาวข้อมูล การทำความสะอาด และงานเงื่อนไขตามความยาวข้อความ
LOWER แปลงข้อความให้เป็นตัวพิมพ์เล็กทั้งหมด มีประโยชน์มากสำหรับการทำความสะอาดข้อมูลและแก้ปัญหาคีย์ไม่ตรงกันเพราะเหตุคำต่างชั้นอักษร
REPLACE แทนที่อักขระจำนวนหนึ่งในสตริง โดยเริ่มจากตำแหน่งที่ระบุ เหมาะกับการแก้ไขข้อความเฉพาะตำแหน่ง เช่น เปลี่ยนคำนำหน้า แทนที่ตัวเลขบางหลัก หรือซ่อนบางส่วนของข้อมูล
REPT ทำซ้ำข้อความตามจำนวนที่กำหนด ช่วยสร้างสัญลักษณ์ซ้ำๆ เช่น ดาว, จุด, หรือเครื่องหมายคั่น เพื่อจัดรูปแบบและเน้นข้อมูลในรายงาน
SEARCH คืนค่าตำแหน่งเริ่มต้นของข้อความที่ค้นหา (ค้นหาแบบไม่สนตัวพิมพ์) หากไม่พบจะเกิดข้อผิดพลาด สามารถใช้กับ IFERROR หรือ COALESCE เพื่อจัดการกรณีไม่พบ
ฟังก์ชันทำความสะอาดข้อความ ลบช่องว่างส่วนเกินนำหน้า ท้าย และช่องว่างหลายตัวระหว่างคำ โดยคงไว้ช่องว่างเดี่ยวระหว่างคำเท่านั้น
UNICHAR รับค่าโค้ด Unicode (ฐานสิบ) และคืนอักขระที่ตรงกับโค้ดนั้น ใช้สร้างสัญลักษณ์หรืออักขระพิเศษใน Measures/Columns เพื่อเพิ่มความสื่อสารในรายงาน
UPPER แปลงข้อความให้เป็นตัวพิมพ์ใหญ่ทั้งหมด ใช้บ่อยเพื่อปรับปรุงคุณภาพข้อมูลและทำให้การเปรียบเทียบข้อความแม่นยำ
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่