Thep Excel
SUBSTITUTE – แทนที่ข้อความที่ตรงกัน
SUBSTITUTE ค้นหาและแทนที่ข้อความในสตริง สามารถแทนที่ทุกครั้งหรือเพียงครั้งที่ n เหมาะกับการปรับรูปแบบและทำความสะอาดข้อมูลโดยตรวจสอบเนื้อหา ไม่ใช่ตำแหน่ง
=SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])
Function Metrics
Popularity
6/10
Difficulty
3/10
Usefulness
6/10
Syntax & Arguments
=SUBSTITUTE(<text>, <old_text>, <new_text>, [<instance_num>])
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| text | text | Yes | ข้อความต้นทางที่ต้องการแทนที่ สามารถเป็นสตริง หรือลิงก์ไปยังคอลัมน์ข้อความ | |
| old_text | text | Yes | ข้อความเดิมที่ต้องการค้นหาและแทนที่ ต้องตรงกับตัวพิมพ์ (case-sensitive) ถ้าไม่ตรง SUBSTITUTE จะไม่ทำงาน | |
| new_text | text | Yes | ข้อความใหม่ที่ใช้แทนที่ old_text สามารถเป็นสตริงว่าง (“”) เพื่อลบข้อความได้ | |
| instance_num | integer | Optional | ครั้งที่ต้องการแทนที่ (เช่น 1 = ครั้งแรก, 2 = ครั้งที่สอง) ถ้าไม่ระบุหรือเป็นค่าลบ จะแทนที่ทุกครั้งที่พบ |
How it works
เปลี่ยนคีย์เวิร์ดหรือแท็กที่ซ้ำ
แทนที่คำเก่าเป็นคำใหม่ในข้อความอธิบายสินค้าหรือบันทึก เพื่อให้ใช้คำมาตรฐาน
จัดรูปแบบข้อความที่ซ้ำหลายครั้ง
แทนที่คำหรือสัญลักษณ์เฉพาะครั้งที่ n เพื่อควบคุมเฉพาะบางตำแหน่ง เช่น เปลี่ยนตัวแบ่งครั้งที่สองเท่านั้น
ทำความสะอาดข้อมูลนำเข้า
ลบหรือแทนที่เครื่องหมายพิเศษจากข้อความนำเข้าก่อนนำไปคำนวณหรือแสดงผล
Examples
แทนที่ทุกครั้งที่พบ
VAR Product = "Samsung Galaxy S24 Samsung Galaxy Watch" RETURN SUBSTITUTE(Product, "Samsung", "Apple")
VAR Product = "Samsung Galaxy S24 Samsung Galaxy Watch"
RETURN SUBSTITUTE(Product, "Samsung", "Apple")
"Apple Galaxy S24 Apple Galaxy Watch"
แทนที่เฉพาะครั้งที่ระบุ
VAR Code = "INV-2024-001-Draft" RETURN SUBSTITUTE(Code, "-", "_", 2)
VAR Code = "INV-2024-001-Draft"
RETURN SUBSTITUTE(Code, "-", "_", 2)
"INV_2024-001-Draft"
ลบเครื่องหมายพิเศษจากข้อมูล
VAR Phone = "(089)-123-4567" RETURN SUBSTITUTE(SUBSTITUTE(Phone, "(", ""), ")", "")
VAR Phone = "(089)-123-4567"
RETURN SUBSTITUTE(SUBSTITUTE(Phone, "(", ""), ")", "")
"089-123-4567"
แทนที่สนตัวพิมพ์
VAR Text = "Power BI power bi POWER BI" RETURN SUBSTITUTE(Text, "Power BI", "Tableau")
VAR Text = "Power BI power bi POWER BI"
RETURN SUBSTITUTE(Text, "Power BI", "Tableau")
"Tableau power bi POWER BI"
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
Resources
-
DAX Guide: SUBSTITUTE
guide -
Microsoft Learn: SUBSTITUTE Function
official
Related functions
Additional Notes
SUBSTITUTE แทนที่ข้อความที่ตรงกันในสตริง ทำงานโดยค้นหา old_text แล้วแทนที่ด้วย new_text โดยสนตัวพิมพ์ (case-sensitive) เหมาะมากเพราะไม่อิงตำแหน่ง ต่างจาก REPLACE ที่ใช้ตำแหน่งไบต์อย่างแน่นอน ยังสามารถระบุ instance_num เพื่อแทนที่เพียงครั้งที่ n ได้
ที่เจ๋งคือ SUBSTITUTE ใช้สำหรับสถานการณ์จริง เช่น เปลี่ยนชื่อเดิมเป็นชื่อใหม่ในข้อมูลขนาดใหญ่ ลบเครื่องหมายพิเศษ หรือทำความสะอาดรูปแบบโดยไม่ต้องรู้ตำแหน่งที่แน่นอน ถ้าต้องเลือกครั้งที่สองจากหลาย ๆ ครั้ง instance_num จะช่วยได้
ส่วนตัวผมชอบใช้ SUBSTITUTE กับ TRIM มากในการทำความสะอาดข้อมูลจาก Excel ก่อนวิเคราะห์ มีข้อสำคัญคือมันสนตัวพิมพ์ ดังนั้นถ้าข้อมูลสับสน ลองใช้ UPPER หรือ LOWER ก่อนแล้วค่อยเทียบ