SUBSTITUTE และ REPLACE ใน Excel

SUBSTITURE และ REPLACE ของ Excel ใช้แทนที่ข้อความเหมือนกัน ต่างกันที่ ถ้าเราทราบตำแหน่งของข้อความเดิมที่จะไปแทนที่ ให้ใช้ Replace แต่ถ้าเราไม่ทราบตำแหน่ง ให้ใช้ Substitute

รูปแบบการใช้งาน

SUBSTITUTE(text, "old_text", "new_text", instance_num)
   text:  ตำแหน่งข้อความที่จะนำมาเปลี่ยน เช่น A1
   old_text: ข้อความเดิมที่ต้องการจะเปลี่ยน
   new_text:  ข้อความที่ต้องการให้เปลี่ยนเป็นข้อความนี้
   instance_num:  ระบุจำนวนครั้งของข้อความที่เกิดขึ้นในข้อความที่จะนำมาเปลี่ยน ที่ต้องการเปลี่ยน (งง เหมือนกัน อ่านต่ออีกซักหน่อยนะครับ)  ถ้าไม่ระบุ Excel จะเปลี่ยนข้อความนั้น ๆ ทีมีอยู่ในข้อความทุกครั้ง เช่น ข้อความเดิมคือ 24122341225 ถ้าต้องการเปลี่ยนเลข 41 เป็น 55 จะมีการเปลี่ยน 2 ครั้ง เพราะหมายเลข 41 มีปรากฏในข้อความที่ระบุ 2 ครั้ง คือ ปราฏครั้งที่ 1 ตำแหน่งที่ 2 และ ปรากฏครั้งที่ 2 ตำแหน่งที่ 7 (ดังนี้ 24122341225) ดังนั้น ถ้าไม่ระบุ instance_num โปรแกรม Excel จะระบุให้ทั้ง 2 แห่ง แต่ถ้าต้องการเปลี่ยนเฉพาะ แต่ถ้าต้องการเปลี่ยนเฉพาะแห่งแรก ต้องระบุเป็น 1 เพราะเท่ากับเป็นการบอกว่า ให้เปลี่ยนเฉพาะที่มันเกิดครั้งแรกเท่านั้น ครั้งต่อ ๆ ไป ถ้าพบก็ไม่ต้องเปลี่ยน


REPLACE(old_text,start_num,num_chars,new_text)
   old_text:  ข้อความที่จะนำมาเปลี่ยน
   start_num: ตำแหน่งแรกที่จะเปลี่ยนในข้อความนั้น
   num_chars: ช่วงที่จะเปลี่ยนนับจากตำแหน่งที่ระบุ
   new_text:  ข้อความใหม่ที่จะนำไปเปลี่ยน

ตัวอย่าง
จากภาพ ให้แทนที่ ตัวเลข 41 ด้วยตัวเลข 000

จะเห็นว่า ตัวเลข 41 ปรากฏอยู่หลายแห่ง และในหลายตำแหน่ง เช่น ที่ A1 อยู่ตำแหน่งที่ 3 ส่วนที่  A3 และ A4 อยู่ตำแหน่งที่ 1 และที่ A13 อยู่ตำแหน่งที่ 2 เป็นต้น

ลักษณะนี้ จะใช้ Replace ไม่น่าจะสะดวก ต้องใช้ Substitute ดีกว่า

ที่ B1 เขียน สูตร ดังนี้

=SUBSTITUTE(A1,"41","000")  (ไม่จำเป็นต้องระบุ instance_num เพราะมี 41 เกิดขึ้นแห่งเดียว)

ลาก หรือ ดับเบิ้ลคลิกที่ปุ่ม Fill handle จะได้ข้อความใหม่ที่แทนที่แล้วทั้งหมด ดังนี้

เพิ่มเติม

จากตัวอย่าง จะเห็นว่ามีการแทนที่เลข 41 ด้วย 000 ทำให้มีจำนวนมากกว่าเดิมไป 1 ตัว ถ้าต้องการรักษาความยาวของข้อความ ให้เท่ากับ 4 ซึ่งเป็นความยาวของข้อความเดิม ให้ใช้ mid()  เพิ่มเข้าไป

สูตร mid() เป็นการกำหนดช่วงข้อความที่จะนำมาให้แสดง มีรูปแบบดังนี้

MID(text,start_num,num_chars)
   text:  ข้อความที่จะนำมาเปลี่ยน
   start_num:  ตำแหน่งแรกที่จะนำมาใช้
   num_chars: ช่วงความยาวที่ต้องการ โดยระบุเป็นจำนวนตัวอักษรที่ต่อจากตำแหน่งแรกที่ระบุ ถ้าเกินจากนั้นไปแล้วให้ตัดทิ้งไป ไม่เอามาใช้

จากตัวอย่าง ต้องการตำแหน่งที่ 1 ถึง 4 เขียนสูตรที่เซลล์ C1 ได้ ดังนี้

=MID(SUBSTITUTE(A1,"41","000"),1,4)

จะได้ ดังภาพ

ลาก Fill handle จะได้ ดังภาพ

จะเห็นว่า Excel อนุญาตให้ใช้สูตรซ้อน ๆ กันได้ ทำให้ได้งานตามเงื่อนไขที่ต้องการ