Thep Excel
SUBSTITUTEแทนที่คำหรือข้อความด้วยคำใหม่
SUBSTITUTE ค้นหาคำเก่า (old_text) ในข้อความแล้วแทนที่ด้วยคำใหม่ (new_text) โดยแยกแยะตัวพิมพ์เล็ก-ใหญ่ (case-sensitive) และเลือกได้ว่าจะแทนที่ทุกจุดที่เจอ หรือเฉพาะลำดับที่ระบุผ่าน instance_num งานที่ใช้บ่อยคือลบตัวคั่นออกจากตัวเลขที่ถูกเก็บเป็นข้อความ เปลี่ยนโดเมนอีเมลหลังรีแบรนด์ หรือล้างอักขระที่พิมพ์มาผิดรูปแบบก่อนนำไปคำนวณต่อ
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| text | Text | ข้อความต้นฉบับ หรือเซลล์ที่ต้องการแก้ไข | |
| old_text | Text | คำเก่าที่ต้องการค้นหาเพื่อเปลี่ยนออก (ต้องใส่เครื่องหมายคำพูด ” “) | |
| new_text | Text | คำใหม่ที่ต้องการนำไปใส่แทนที่ | |
| [instance_num]ไม่บังคับ | Number | All | ลำดับของคำที่ต้องการเปลี่ยน (เช่น 1 = เปลี่ยนเฉพาะคำแรก) ถ้าไม่ระบุจะเปลี่ยนทั้งหมด |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
ฟังก์ชัน SUBSTITUTE แทนที่ข้อความเก่าที่ระบุด้วยข้อความใหม่ โดยค้นหาจาก “คำที่ตรงกัน” ไม่ใช่ตำแหน่ง เหมาะกับงานล้างข้อมูล เช่น ลบขีดออกจากเบอร์โทร ลบคอมม่าออกจากตัวเลขที่ถูกเก็บมาเป็นข้อความก่อนแปลงเป็นตัวเลขจริง หรือแก้ชื่อ/รหัสสินค้าที่เปลี่ยนคำเรียกไปทั้งไฟล์ในทีเดียว จุดที่ต้องระวังคือมันแยกตัวพิมพ์เล็ก-ใหญ่ ถ้าตัวสะกดหรือ case ไม่ตรงกับ old_text เป๊ะ SUBSTITUTE จะคืนข้อความเดิมกลับมาเฉยๆ โดยไม่ฟ้อง error ให้รู้ตัว
How it works
ทำความสะอาดข้อมูล (Clean Data)
ลบช่องว่างส่วนเกิน, ลบเครื่องหมายวรรคตอน, หรือแก้ไขคำผิดที่พบบ่อยในฐานข้อมูลลูกค้า
นับจำนวนคำในประโยค
ใช้สูตร =LEN(Text) – LEN(SUBSTITUTE(Text, " ", "")) + 1 เพื่อหาว่าในประโยคมีช่องว่างกี่ตัว แล้วบวก 1 จะได้จำนวนคำคร่าวๆ
Examples
เปลี่ยนปี พ.ศ. เป็น ค.ศ.SUBSTITUTE("ปี 2567", "2567", "2024")
ค้นหาคำว่า "2567" ในข้อความ แล้วแทนที่ด้วย "2024"
ลบขีดออกจากเบอร์โทรศัพท์SUBSTITUTE("081-123-4567", "-", "")
แทนที่ขีด "-" ด้วยความว่างเปล่า "" (Empty String) เพื่อลบตัวอักษรที่ไม่ต้องการออกให้เหลือแต่ตัวเลข
เปลี่ยนเฉพาะคำแรก (Instance)SUBSTITUTE("Team A vs Team B", "Team", "Group", 1)
ระบุ instance_num เป็น 1 เพื่อสั่งให้เปลี่ยนคำว่า "Team" เฉพาะครั้งแรกที่เจอเท่านั้น คำหลังจะไม่ถูกเปลี่ยน
แทนที่การขึ้นบรรทัดใหม่ด้วยช่องว่างSUBSTITUTE(A2, CHAR(10), " ")
ใช้ CHAR(10) แทนรหัสของการขึ้นบรรทัดใหม่ (Line Break) แล้วเปลี่ยนให้เป็นช่องว่าง " " เพื่อจัดข้อความให้อยู่ในบรรทัดเดียว มักเจอตอนก็อปข้อมูลจากระบบอื่นที่แทรกการขึ้นบรรทัดมาในเซลล์เดียว
แปลงตัวเลขที่มีคอมม่าคั่นหลักพันให้เป็นตัวเลขจริง (งานบัญชี)SUBSTITUTE(A2,",","")+0
สมมติ A2 เก็บค่า "1,234,567" มาเป็นข้อความ (มักเจอตอนก็อปจากรายงานหรือไฟล์ CSV) SUBSTITUTE ลบคอมม่าออกให้เหลือ "1234567" แต่ผลลัพธ์ยังเป็นข้อความอยู่ ต้องบวก +0 ต่อท้ายเพื่อบังคับให้ Excel แปลงเป็นตัวเลขจริง ไม่งั้น SUM หรือสูตรคำนวณอื่นจะไม่นับค่านี้
FAQs
ต่างจากฟังก์ชัน REPLACE อย่างไร?+
SUBSTITUTE เปลี่ยนโดย "ค้นหาคำ" (ไม่สนตำแหน่ง) ส่วน REPLACE เปลี่ยนโดย "ระบุตำแหน่ง" (เช่น เปลี่ยนตัวอักษรที่ 5 ถึง 8) เลือก SUBSTITUTE เมื่อรู้ว่าคำที่จะเปลี่ยนคืออะไรแต่ไม่รู้ตำแหน่งแน่ชัด และเลือก REPLACE เมื่อรู้ตำแหน่งแน่นอน เช่น ตัดโค้ดสาขา 3 ตัวแรกออกจากรหัสสินค้าที่ความยาวคงที่
สนใจตัวพิมพ์เล็ก-ใหญ่ไหม (Case Sensitive)?+
สนใจครับ "Apple" ไม่เท่ากับ "apple" ที่อันตรายคือถ้า case ไม่ตรง SUBSTITUTE จะไม่ฟ้อง error ใดๆ แค่คืนข้อความต้นฉบับกลับมาเหมือนไม่มีอะไรเกิดขึ้น ทำให้พลาดง่ายมากถ้าไม่ตรวจผลลัพธ์ ถ้าต้องการเปลี่ยนแบบไม่สน case ให้ห่อทั้ง text และ old_text ด้วย LOWER() ก่อนเทียบ แล้วค่อยใช้ SUBSTITUTE กับ old_text ตัวพิมพ์เล็กเดียว
SUBSTITUTE เปลี่ยนได้หลายคำในครั้งเดียวไหม?+
ไม่ได้ครับ หนึ่ง SUBSTITUTE เปลี่ยนได้แค่คำเดียวต่อครั้ง ถ้าต้องเปลี่ยนหลายคำในสูตรเดียว ต้องซ้อน SUBSTITUTE หลายชั้นเข้าไปในกันและกัน (nested) แต่ละชั้นจัดการคำละคำไปเรื่อยๆ
ทำไมใช้ SUBSTITUTE ลบคอมม่าออกจากตัวเลขแล้ว SUM ยังไม่รวมค่านั้นให้?+
เพราะ SUBSTITUTE คืนผลลัพธ์เป็นข้อความ (text) เสมอ ต่อให้หน้าตาเหมือนตัวเลขก็ตาม Excel ไม่แปลงให้อัตโนมัติ ต้องบวก +0 หรือคูณ *1 ต่อท้ายสูตร หรือห่อด้วย VALUE() เพื่อบังคับแปลงเป็นตัวเลขจริงก่อน SUM ถึงจะนับรวมได้
ใช้ SUBSTITUTE กับ wildcard (* หรือ ?) แบบที่ใช้ใน Find & Replace ได้ไหม?+
ไม่ได้ครับ SUBSTITUTE เทียบแบบตรงตัวอักษรต่ออักษรเท่านั้น ไม่รองรับ wildcard เหมือนกล่อง Find & Replace (Ctrl+H) ถ้าต้องการค้นหาแบบมีรูปแบบ (pattern) เช่น ตัวเลขกี่หลักก็ได้ ต้องใช้ REGEXREPLACE แทน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ SUBSTITUTE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CHAR แปลงรหัสตัวอักษร (1-255) เป็นตัวอักษรจริง มีประโยชน์สำหรับแทรกอักขระพิเศษที่พิมพ์ยาก เช่น การขึ้นบรรทัดใหม่
ลบตัวอักษรที่ไม่สามารถพิมพ์ได้ (ASCII 0-31) ออกจากข้อความ มีประโยชน์เมื่อคัดลอกข้อมูลจากระบบอื่นที่มีอักขระซ่อนอยู่
EXACT เปรียบเทียบข้อความสองข้อความว่าเหมือนกันทุกประการหรือไม่ โดยสนใจตัวพิมพ์ใหญ่-เล็ก (Case-sensitive) คืนค่า TRUE ถ้าเหมือนกัน FALSE ถ้าต่างกัน
FIND ค้นหาตำแหน่งเริ่มต้นของคำที่ต้องการภายในข้อความหลัก โดยสนใจตัวพิมพ์เล็ก-ใหญ่ (เช่น "A" ไม่เหมือนกับ "a") ถ้าค้นหาไม่เจอจะคืนค่า Error #VALUE! มักใช้ร่วมกับ MID, LEFT, RIGHT เพื่อตัดคำตามตำแหน่ง
LEN คืนค่าเป็นตัวเลขจำนวนเต็ม แสดงความยาวของข้อความในเซลล์ มีประโยชน์มากในการตรวจสอบความถูกต้องของข้อมูล (Data Validation) เช่น เช็ครหัสพนักงาน, เบอร์โทรศัพท์, หรือเลขบัตรประชาชน ว่ามีความยาวครบถ้วนหรือไม่
MID ตัดข้อความออกจากตำแหน่งเริ่มต้นที่คุณกำหนด โดยระบุความยาวของข้อความที่ต้องการ สะดวกมากสำหรับดึงข้อมูลบางส่วนจากข้อความที่ยาว เช่น รหัสสินค้า, รหัสพนักงาน, หรือวันที่ที่ฝังตัวในข้อความ
PROPER แปลงตัวอักษรแรกของแต่ละคำเป็นตัวพิมพ์ใหญ่ (Title Case) และแปลงตัวอักษรที่เหลือเป็นตัวพิมพ์เล็ก เหมาะสำหรับจัดรูปแบบชื่อคน ชื่อสถานที่ ใช้ร่วมกับ UPPER LOWER TRIM เพื่อทำความสะอาดข้อมูล
REGEXEXTRACT เป็นฟังก์ชันสำหรับดึงข้อความย่อย (Substring) ที่ตรงกับรูปแบบ Regular Expression (Regex) ที่กำหนด เหมาะสำหรับการทำ Data Cleaning ขั้นสูง
REGEXREPLACE ค้นหาและแทนที่ข้อความจากรูปแบบ Regular Expression ใช้สำหรับ Data Cleaning ที่ซับซ้อน เหมาะสำหรับ Excel 365 รองรับ backreferences
REPLACE แทนที่ข้อความจากตำแหน่งที่กำหนด โดยระบุตำแหน่งเริ่มต้น จำนวนตัวอักษรที่ต้องการลบ และข้อความใหม่ที่จะใส่แทน แตกต่างจาก SUBSTITUTE ที่ค้นหาคำที่ตรงตามความพึงพอใจ REPLACE ใช้ตำแหน่งแน่นอน
REPT ทำซ้ำข้อความตามจำนวนครั้งที่ระบุ เหมาะสำหรับสร้าง In-cell Bar Chart แสดง Rating ด้วยดาว เติม Padding ให้ข้อความ หรือสร้างเส้นแบ่ง ถ้า number_times เป็นทศนิยมจะถูกตัดเหลือจำนวนเต็ม
SEARCH ค้นหาตำแหน่งของคำที่ต้องการในข้อความหลัก ถ้าเจอคืนตัวเลขตำแหน่งที่พบ ถ้าไม่เจอคืน #VALUE! ต่างจาก FIND ตรงที่ไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่ (A=a) และใช้ wildcard (* กับ ?) ค้นหาแบบมีรูปแบบได้ เอาไปต่อกับ ISNUMBER ก็กลายเป็นสูตรเช็คว่าเซลล์มีคำนี้อยู่หรือไม่ ซึ่งเป็นวิธีที่ใช้กันบ่อยที่สุดในงานจัดหมวดหมู่หรือกรองข้อมูลจากคำสำคัญ
TEXT ใช้รหัสรูปแบบ (Format Codes) เช่น "dd/mm/yyyy" สำหรับวันที่ หรือ "#,##0.00" สำหรับตัวเลขมีทศนิยม ผลลัพธ์ที่ได้จะเป็นข้อความ (Text) เสมอ ไม่สามารถนำไปคำนวณต่อได้
แปลข้อความจากภาษาหนึ่งไปอีกภาษาหนึ่งโดยตรงในสูตร Excel ผ่าน Microsoft Translation Services เหมาะกับการแปลไทย-อังกฤษแบบไดนามิกโดยไม่ต้องสลับไปแอปอื่น
TRIM ลบช่องว่างที่ด้านหน้า ด้านหลัง และลดช่องว่างระหว่างคำให้เหลือเพียงเคาะเดียว เหมาะสำหรับทำความสะอาดข้อมูลที่ Copy จากเว็บหรือระบบอื่น
VALUE แปลงข้อความที่มีลักษณะเป็นตัวเลขให้เป็นตัวเลขจริงที่คำนวณได้ รองรับรูปแบบตัวเลข วันที่ เวลา เปอร์เซ็นต์ และสกุลเงิน
แปลงค่าใดๆ (ตัวเลข, วันที่, อาร์เรย์, หรือแม้กระทั่ง Error) ให้เป็นข้อความเพื่อใช้งานได้จริง
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่