คลังสูตรExcel 80 · DAX 95 · M 15
ในหน้านี้
หน้าแรก › ฟังก์ชัน › ข้อความ › SUBSTITUTE

SUBSTITUTE

ข้อความระดับกลางทุกเวอร์ชัน

อัปเดต 24 ก.ย. 2569 · ตรวจ syntax และเวอร์ชันกับเอกสารของ Microsoft · ผลในตัวอย่างตรวจด้วยเครื่องมือคำนวณ 2 ชุด

SUBSTITUTE คืออะไร

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

ใช้เมื่อไหร่

  • ลบขีด ช่องว่าง หรือคำที่ไม่ต้องการออกจากรหัสและข้อความ
  • ล้างยอดเงินที่เป็นข้อความ เช่น 12,450 บาท ให้กลายเป็นตัวเลขที่รวมได้
  • นับจำนวนรายการในช่องที่คั่นด้วยจุลภาค

รูปแบบสูตร

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Argument แต่ละตัว

ชี้ที่ argument ในรูปแบบสูตรด้านบนเพื่อไฮไลต์แถวนั้น

Argumentจำเป็นไหมความหมาย
textจำเป็นข้อความหรือช่องที่ต้องการแก้
old_textจำเป็นข้อความเดิมที่ต้องการแทน ตัวพิมพ์เล็กใหญ่ต้องตรง
new_textจำเป็นข้อความใหม่ ใส่ "" เมื่อต้องการลบ
[instance_num]ไม่บังคับแทนเฉพาะครั้งที่เท่าไร ถ้าไม่ใส่ = แทนทุกจุด

ลองเลย: ตัวอย่างที่เปลี่ยนค่าได้

ข้อมูลชุดเดียวกับไฟล์ฝึก เปลี่ยนค่าในช่องกรอก หรือคลิกตัวเลขในตารางเพื่อแก้ แล้วดูผลลัพธ์เปลี่ยนทันที กดถัดไปเพื่อดูว่า Excel คิดทีละขั้นอย่างไร สีของช่วงในตารางตรงกับสีในสูตร

ทำเลขอ้างอิงสำหรับชำระเงินที่ไม่มีขีด โดยแทนขีดทุกตัวด้วยข้อความว่าง

I5
=SUBSTITUTE(I4,"SF-","")
ข้อความต้นทาง และคำที่จะแทน I4 = "SF-2025-0734" หา old_text "SF-" เจอ 1 ครั้ง ที่ตัวที่ 1
ABCDEFG
4เลขที่คำสั่งซื้อวันที่พนักงานขายยอดสุทธิ (บาท)เลขอ้างอิง
5SF-2025-07341/12/2025สุภาพร หงษ์ทอง355,320SF20250734
6SF-2025-07382/12/2025กมลชนก พงษ์พานิช1,235,098SF20250738
7SF-2025-07393/12/2025นันทิดา กิจเจริญ22,878SF20250739
8SF-2025-07414/12/2025นันทิดา กิจเจริญ298,740SF20250741
9SF-2025-07446/12/2025สุภาพร หงษ์ทอง269,705SF20250744
10SF-2025-07508/12/2025กมลชนก พงษ์พานิช522,200SF20250750
11SF-2025-07569/12/2025สุภาพร หงษ์ทอง1,057,572SF20250756
12SF-2025-076011/12/2025กมลชนก พงษ์พานิช1,049,152SF20250760
13SF-2025-077415/12/2025นันทิดา กิจเจริญ334,720SF20250774
14SF-2025-078419/12/2025กมลชนก พงษ์พานิช289,275SF20250784
15
16
17
18
19
ช่องกรอกและผลลัพธ์
ตัดคำนำหน้า SF- ออก I5
พิมพ์ sf- ตัวพิมพ์เล็ก I6
ขั้นตอนการคำนวณ (3)
คลิกช่องสีเขียวมะนาวในตารางเพื่อดูสูตรของแถวนั้น · คลิกตัวเลขเพื่อแก้ค่า · แก้สูตรได้ที่แถบสูตร หรือพิมพ์ = ในช่องว่าง
100%
ข้อมูลสมมติของสยามเฟรช ชุดเดียวกับชีต “1 ลบขีด” ในไฟล์ฝึก (ปี ค.ศ.)
  • อ่านสูตร E5: แทนขีด (-) ทุกตัวใน A5 ด้วย "" (ข้อความว่าง) ขีดจึงหายไป
  • I6 ไม่เปลี่ยนเลย เพราะ SUBSTITUTE แยกตัวพิมพ์เล็กใหญ่ sf- จึงไม่ตรงกับ SF-
  • ผลของ SUBSTITUTE เป็นข้อความเสมอ ถ้าต้องการนำไปคำนวณ ดูวิธีแปลงเป็นตัวเลขในตัวอย่าง 2

ตัวอย่างทั้งหมด 4 แบบ

  1. 1ลบขีดออกจากเลขที่คำสั่งซื้อ · ทำเลขอ้างอิงสำหรับชำระเงินที่ไม่มีขีด โดยแทนขีดทุกตัวด้วยข้อความว่าง
  2. 2แปลงยอดที่พิมพ์ว่า บาท ให้เป็นตัวเลข · ยอดที่ร้านค้าแจ้งมาเป็นข้อความ เช่น 12,450 บาท รวมไม่ได้ SUBSTITUTE สองชั้นลบจุลภาคและคำว่า บาท แล้ว VALUE แปลงเป็นตัวเลข
  3. 3นับจำนวนร้านที่เข้าเยี่ยมจากช่องเดียว · ลบจุลภาคออกแล้วดูว่าข้อความสั้นลงกี่ตัว ได้จำนวนจุลภาค บวก 1 เป็นจำนวนร้าน
  4. 4แทนเฉพาะจุลภาคตัวสุดท้ายด้วยคำว่า และ · instance_num บอกว่าให้แทนครั้งที่เท่าไร ใส่จำนวนจุลภาคลงไปจึงแทนเฉพาะตัวสุดท้าย ได้ประโยคพร้อมใช้ในรายงาน

ข้อผิดพลาดที่พบบ่อย

แทนไม่ครบบางแถวสาเหตุ: SUBSTITUTE แยกตัวพิมพ์เล็กใหญ่ inv กับ INV ถือว่าไม่เหมือนกันแก้: แปลงเป็นตัวพิมพ์เดียวกันก่อนด้วย UPPER แล้วค่อยแทน
#VALUE!สาเหตุ: instance_num เป็น 0 หรือติดลบแก้: ใส่ตั้งแต่ 1 ขึ้นไป หรือไม่ใส่เพื่อแทนทุกจุด
SUM รวมผลลัพธ์ได้ 0สาเหตุ: SUBSTITUTE ให้ผลเป็นข้อความเสมอ แม้หน้าตาเป็นตัวเลข SUM จึงข้ามไปแก้: ครอบด้วย VALUE เพื่อแปลงเป็นตัวเลข
นับรายการได้ 1 ทั้งที่ไม่มีข้อมูลสาเหตุ: สูตร LEN − LEN(SUBSTITUTE()) + 1 บวก 1 เสมอแก้: ครอบด้วย IF เช่น =IF(C5="",0,…)

เคล็ดลับจากห้องเรียน

แทนหลายอย่างในสูตรเดียว ให้ซ้อน SUBSTITUTE หลายชั้น ชั้นในทำก่อน
SUBSTITUTE แทนตามข้อความที่เจอ ส่วน REPLACE แทนตามตำแหน่งและจำนวนตัวอักษร
แก้ครั้งเดียวทั้งคอลัมน์ใช้ Find and Replace (Ctrl + H บน Windows) ได้ แต่สูตร SUBSTITUTE อัปเดตเองเมื่อข้อมูลเปลี่ยน

ใช้กับ Excel ของคุณได้ไหม

เวอร์ชันExcel 2016Excel 2019Excel 2021Excel 2024Microsoft 365
SUBSTITUTE

SUBSTITUTE ใช้ได้ทุกเวอร์ชันที่ยังใช้กันอยู่ จึงไม่ต้องมีสูตรทางเลือก

เลือกวิธีให้ถูกงาน

วิธีใช้เมื่อตัวอย่าง
SUBSTITUTEแทนตามข้อความที่เจอ ทุกจุดหรือเฉพาะครั้งที่ระบุ=SUBSTITUTE(A5,"-","")
REPLACEแทนตามตำแหน่งและจำนวนตัวอักษร=REPLACE(A5,1,3,"")
TRIMตัดเฉพาะช่องว่างที่เกิน=TRIM(A5)
Find and Replaceแก้ข้อมูลต้นทางครั้งเดียว ไม่มีสูตรCtrl + H

ใน Power BI ใช้อะไร

แบบ 1 · ใช้ได้เหมือนกันSUBSTITUTE

ชื่อเดียวกันใน DAX ใช้ใน Calculated Column ได้ งานล้างข้อมูลจำนวนมากควรทำใน Power Query ก่อนโหลด

Power QueryTable.ReplaceValue

แทนค่าในคอลัมน์ที่เลือก ทั้งแบบทั้งช่องและบางส่วนของข้อความ

M · Table.ReplaceValue

ไฟล์ฝึก SUBSTITUTE

9Expert-SUBSTITUTE-Practice-v1.2.xlsx

รุ่น 1.2 · 24/9/2026 · 28 KB · ไม่มี Macro · ข้อมูลชุดเดียวกับตัวอย่างบนเว็บ · มีเฉลยและตรวจคำตอบอัตโนมัติ
เริ่มต้นที่นี่1 ลบขีด2 ยอดเงินเป็นตัวเลข3 นับรายการ4 แทนครั้งที่ nแบบฝึกหัดเฉลยสรุปย่อ

โจทย์ในชีตแบบฝึกหัด

  1. ข้อ 1เปลี่ยนขีดในเลขที่คำสั่งซื้อแถว 7 เป็นเครื่องหมาย /
  2. ข้อ 2ตัดคำนำหน้า SF- ออกจากเลขที่คำสั่งซื้อแถว 8
  3. ข้อ 3เปลี่ยนเฉพาะขีดตัวที่ 2 ในเลขที่คำสั่งซื้อแถว 9 เป็นเครื่องหมาย /
  4. ข้อ 4ตัดคำว่า สยามเฟรช ออกจากชื่อสินค้าแถว 8
  5. ข้อ 5ชื่อสินค้าแถว 10 มีช่องว่างกี่ช่อง

วิดีโอประกอบ

คลิปเรื่อง SUBSTITUTE จากช่อง YouTube @9expert

ในเว็บจริงจะฝังคลิปพร้อมตั้งเวลาเริ่มตรงหัวข้อนี้ ในต้นแบบเปิดผลค้นหาในช่องแทน

เปิดใน YouTube

แบบทดสอบ 3 ข้อ

1. =SUBSTITUTE("SF-2025-0734","-","") ได้อะไร

2. =SUBSTITUTE("SF-2025-0734","-","/",2) ได้อะไร

3. =SUBSTITUTE("Inv-0412","inv","INV") ได้อะไร

ไปต่อ

TRIM · หน้าเต็มLEN · หน้าเต็มVALUE · หน้าเต็มFIND · หน้าเต็มREGEXREPLACE
อยากใช้สูตรเหล่านี้กับงานจริงทั้งระบบ

ต่อยอดใน Microsoft Excel Advanced: สูตรสรุปผลแบบมีเงื่อนไข สูตรสืบค้นข้อมูล สูตรใหม่ใน Microsoft 365 และการจัดการ Error (2 วัน)

ดูหลักสูตร
หน้านี้ช่วยได้ไหม