แทนที่ข้อความด้วย SUBSTITUTE
แทนที่อักขระหรือคำภายในสตริงด้วย SUBSTITUTE
แทนที่ข้อความด้วย SUBSTITUTE เป็นบทเรียน Excel Formulas Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Excel Formulas Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
เหตุผลที่ต้องแทนที่ข้อความ
ข้อมูลมักต้องค้นหาและแทนที่ภายในสูตร เช่น ลบขีดกลางออกจากหมายเลขโทรศัพท์ เปลี่ยนคำบางคำ หรือลบสัญลักษณ์ที่เกินมา
ฟังก์ชัน SUBSTITUTE ค้นหาข้อความบางส่วนภายในเซลล์ แล้วแทนที่ทุกตำแหน่งที่พบด้วยข้อความใหม่ โดยไม่เปลี่ยนแปลงเซลล์ต้นฉบับ
ฟังก์ชันนี้เทียบเท่ากับการค้นหาและแทนที่ด้วยคำสั่ง ค้นหา & แทนที่ แต่จะอัปเดตโดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลง
ไวยากรณ์ของ SUBSTITUTE
SUBSTITUTE รับอาร์กิวเมนต์ได้สูงสุดสี่รายการ:
- ข้อความ สตริงหรือเซลล์ต้นฉบับ
- ข้อความเดิม ข้อความที่ต้องการค้นหา
- ข้อความใหม่ ข้อความที่จะนำมาแทนที่
- หมายเลขครั้งที่ (ไม่บังคับ) ตำแหน่งที่พบซึ่งต้องการแทนที่
รูปแบบคือ =SUBSTITUTE(text, old_text, new_text, [instance_num]) หากละอาร์กิวเมนต์สุดท้าย ระบบจะแทนที่ทุกตำแหน่งที่พบ
=SUBSTITUTE(A2, "-", "")การลบอักขระ
เทคนิคที่ใช้กันทั่วไปคือแทนที่สิ่งที่ต้องการลบด้วย ไม่มีข้อความ โดยกำหนดให้ข้อความใหม่เป็นสตริงว่าง ""
หาก A2 มีค่าเป็น 555-123-4567 สูตรด้านล่างจะลบขีดกลางทุกตัว และคืนค่าเป็น 5551234567
รูปแบบนี้ช่วยทำความสะอาดหมายเลขโทรศัพท์ รหัสประจำตัว และค่าประเภทอื่น ๆ ที่มีเครื่องหมายคั่น
=SUBSTITUTE(A2, "-", "")การเปลี่ยนคำหนึ่งเป็นอีกคำหนึ่ง
SUBSTITUTE ใช้กับคำทั้งคำได้เช่นกัน ไม่ได้จำกัดเฉพาะอักขระเดี่ยว
หาก A2 มีค่าเป็น รายงานประจำปี 2023 คุณสามารถอัปเดตปีได้ด้วยการแทนที่คำดังกล่าว:
ผลลัพธ์คือ รายงานประจำปี 2024 ระบบจะเปลี่ยนทุกตำแหน่งที่ตรงกันภายในเซลล์
=SUBSTITUTE(A2, "2023", "2024")SUBSTITUTE แยกแยะตัวพิมพ์เล็ก-ใหญ่
สำคัญ: SUBSTITUTE จะจับคู่ข้อความแบบ ตรงกันทุกประการ รวมถึงรูปแบบตัวพิมพ์
หาก A2 มีค่าเป็น แมว แมว CAT แล้ว =SUBSTITUTE(A2, "cat", "dog") จะเปลี่ยนเฉพาะ แมว ที่เป็นตัวพิมพ์เล็กเท่านั้น ทำให้ได้ผลลัพธ์เป็น แมว หมา CAT
หากต้องการแทนที่โดยไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่ ให้ปรับรูปแบบตัวพิมพ์ให้เป็นมาตรฐานด้วย LOWER หรือ UPPER ก่อน หรือใช้ฟังก์ชันนิพจน์ทั่วไปใน Sheets จากบทเรียนถัดไป
=SUBSTITUTE(A2, "cat", "dog")การแทนที่เพียงตำแหน่งเดียว
อาร์กิวเมนต์รายการที่สี่ซึ่งไม่บังคับ คือ หมายเลขครั้งที่ ช่วยให้คุณระบุตำแหน่งที่พบเพียงตำแหน่งเดียวแทนการแทนที่ทั้งหมด
หาก A2 มีค่าเป็น a-b-c-d และคุณต้องการเปลี่ยนเฉพาะขีดกลาง ตัวที่สอง ให้กำหนดหมายเลขครั้งที่เป็น 2
ผลลัพธ์คือ a-b/c-d โดยมีเพียงขีดกลางตัวที่สองเท่านั้นที่เปลี่ยนเป็นเครื่องหมายทับ
=SUBSTITUTE(A2, "-", "/", 2)ตัวอย่างการทำงาน: การทำความสะอาดตัวเลข
ลองนึกภาพจำนวนเงินที่นำเข้าและจัดเก็บเป็นข้อความ: $1,250.00 หากต้องการเปลี่ยนให้เป็นตัวเลขที่ใช้งานได้ คุณต้องลบเครื่องหมายดอลลาร์และเครื่องหมายจุลภาคออก
ซ้อนการเรียกใช้ SUBSTITUTE สองครั้ง โดยให้การเรียกใช้ครั้งหนึ่งอยู่ภายในการเรียกใช้อีกครั้ง เพื่อลบอักขระทั้งสองตัว
การเรียกใช้ด้านในจะลบเครื่องหมายจุลภาค ส่วนการเรียกใช้ด้านนอกจะลบเครื่องหมายดอลลาร์ เหลือเป็น 1250.00
=SUBSTITUTE(SUBSTITUTE(A2, ",", ""), "$", "")การเชื่อมการแทนที่หลายรายการ
คุณสามารถซ้อนการเรียกใช้ SUBSTITUTE ได้มากเท่าที่จำเป็น โดยแต่ละชั้นจะทำความสะอาดอักขระคนละตัว
หากต้องการปรับรหัสที่ไม่เป็นระเบียบและมีช่องว่าง จุด และเครื่องหมายทับ ให้เป็นขีดกลางที่สม่ำเสมอ ให้เชื่อมการแทนที่สามรายการเข้าด้วยกัน
แต่ละชั้นจะส่งผลลัพธ์ต่อไปยังชั้นถัดไป แล้วค่อย ๆ สร้างสตริงที่ทำความสะอาดแล้วเป็นผลลัพธ์สุดท้าย
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", "-"), ".", "-"), "/", "-")SUBSTITUTE กับ REPLACE
อย่าสับสนระหว่าง SUBSTITUTE กับฟังก์ชัน REPLACE ที่มีลักษณะคล้ายกัน
SUBSTITUTEแทนที่ข้อความโดย จับคู่จากเนื้อหา (ค้นหาคำนี้แล้วเปลี่ยนเป็นคำอื่น)REPLACEแทนที่ข้อความโดยอิงจาก ตำแหน่ง (แทนที่อักขระ 4 ตัวโดยเริ่มจากตำแหน่งที่ 5)
ใช้ SUBSTITUTE เมื่อคุณรู้ว่า ต้องการค้นหาอะไร และใช้ REPLACE เมื่อคุณรู้ว่า สิ่งนั้นอยู่ที่ตำแหน่งใด
=SUBSTITUTE(A2, "old", "new")การนับจำนวนตำแหน่งที่พบด้วย SUBSTITUTE
เคล็ดลับเพิ่มเติมที่น่าสนใจคือการใช้ SUBSTITUTE ร่วมกับ LEN เพื่อ นับว่าบางสิ่งปรากฏกี่ครั้ง
นำความยาวหลังจากลบอักขระออกไปลบจากความยาวเดิม หาก A2 มีค่าเป็น a,b,c,d วิธีนี้จะนับเครื่องหมายจุลภาคและคืนค่าเป็น 3
เทคนิคนี้เหมาะอย่างยิ่งสำหรับนับรายการในรายการที่คั่นด้วยตัวคั่น
=LEN(A2) - LEN(SUBSTITUTE(A2, ",", ""))งานจริง: การทำรูปแบบหมายเลขโทรศัพท์ให้เป็นมาตรฐาน
สมมติว่า A2 มีหมายเลขที่ไม่เป็นระเบียบและมีจุดกับช่องว่าง เช่น 555.123 4567 คุณต้องการรูปแบบที่มีขีดกลางสม่ำเสมอเป็น 555-123-4567
ขั้นแรกให้ลบจุดและช่องว่างออก จากนั้นคุณอาจใส่ขีดกลางกลับเข้าไปใหม่ แต่ในตอนนี้ให้ปรับตัวคั่นทั้งหมดให้เป็นขีดกลางแบบเดียวกันก่อน
การซ้อนการแทนที่จะช่วยทำให้ตัวคั่นที่ไม่เป็นระเบียบทุกชนิดกลายเป็นรูปแบบมาตรฐาน
=SUBSTITUTE(SUBSTITUTE(A2, ".", "-"), " ", "-")ตรวจสอบความเข้าใจ
ทดสอบความเข้าใจเกี่ยวกับ SUBSTITUTE
ทบทวน: การแทนที่ข้อความด้วย SUBSTITUTE
คุณได้เรียนรู้วิธีค้นหาและแทนที่ข้อความในสูตร:
=SUBSTITUTE(text, old_text, new_text, [instance_num])- ใช้
""เป็นข้อความใหม่เพื่อ ลบ อักขระ - ฟังก์ชันนี้ แยกแยะตัวพิมพ์เล็ก-ใหญ่ หากจำเป็น ให้ปรับรูปแบบตัวพิมพ์ให้เป็นมาตรฐานก่อน
- หมายเลขครั้งที่ซึ่งไม่บังคับจะระบุตำแหน่งที่พบเพียงตำแหน่งเดียว
- ซ้อนการเรียกใช้เพื่อทำความสะอาดอักขระหลายตัว และใช้ร่วมกับ
LENเพื่อนับจำนวนตำแหน่งที่พบ
คุณเรียนจบบทการเชื่อมและการเปลี่ยนข้อความแล้ว ทำได้ยอดเยี่ยมมากในการสร้างข้อมูลที่สะอาดและสม่ำเสมอ
คำถามที่พบบ่อย
บทเรียน “แทนที่ข้อความด้วย SUBSTITUTE” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “แทนที่ข้อความด้วย SUBSTITUTE” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Excel Formulas Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “แทนที่ข้อความด้วย SUBSTITUTE”
แทนที่อักขระหรือคำภายในสตริงด้วย SUBSTITUTE คุณปฏิบัติ Excel Formulas Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Excel Formulas Academy หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน Excel Formulas Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน
บทเรียน “แทนที่ข้อความด้วย SUBSTITUTE” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน Excel Formulas Academy นี้ได้ไหม
ได้ บทเรียน Excel Formulas Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- รวมข้อความด้วย CONCAT
- เชื่อมด้วยตัวคั่นโดยใช้ TEXTJOIN
- เปลี่ยนตัวพิมพ์ด้วย UPPER, LOWER, PROPER
- แทนที่ข้อความด้วย SUBSTITUTE