ฟังก์ชันแทนที่และตัดข้อความใน Excel SUBSTITUTE, CLEAN และ TRIM

การเอาช่องว่างและอักขระที่ไม่พิมพ์ออกจากข้อความ

บางครั้งการคัดลอกข้อมูลมาวางบน Excel worksheet หรือข้อมูลที่ได้จากการบันทึกอาจมีช่องว่างนำหน้าหรือต่อท้าย ข้อความ, วันที่ หรือ จำนวนตัวเลข ฯลฯ

ซึ่งไม่สามารถนำมาใช้งานได้ทันที ต้องมาแก้ไขเอาช่องว่างที่ไม่ต้องการออก และการที่จะแก้ไขทีละรายการกับข้อมูลที่มีปริมาณมากๆ จะเป็นการเสียเวลามาก


ซึ่งงานลักษณะนี้เราสามารถใช้บริการฟังก์ชันชุด TRIM, CLEAN และ SUBSTITUTE ช่วยตัดอักขระหรือช่องว่างเหล่านี้ออก จะใช้เดี่ยวๆ หรือใช้รวมกันได้ โดยที่อาจพ่วงการใช้งานร่วมกับตัวอักขระ ASCII ซึ่งมี 255 อักขระ

Function	ไวยากรณ์และการทำงานของฟังก์ชัน TRIM, CLEAN
TRIM	=TRIM(text), text คือ ข้อความที่ต้องการเอาช่องว่างออก
	ฟังก์ชัน Trim จะเอาช่องว่างออกจากข้อความ ยกเว้นช่องว่างระหว่างคำ 1 เคาะ
CLEAN	=CLEAN(text), text คือ ข้อความที่ต้องการเอาอักขระที่พิมพ์ไม่ได้ออก
	ฟังก์ชัน Clean จะตัดออกตัวอักขระ ASCII ซึ่งถูกกำหนดให้ใช้กับฟังก์ชั่น CHAR(0)-CHAR(31)

ตัวอย่างการใช้ TRIM และ CLEAN

	A	B	C	D
1	ข้อมูล	การใช้ฟังก์ชัน	ผลลัพท์	คำอธิบายการใช้ฟังก์ชัน TRIM, CLEAN
2	Year  2013	=TRIM(A2)	Year 2013	ช่องว่างก่อนและหลัง Year ลดลง 1 ช่อง
3	Hello	=CLEAN(A3)	Hello	เป็นอักขระที่ไม่พิมพ์หายไป (ค่า ASCll = 2, 7)

Function	ไวยากรณ์และการทำงานของฟังก์ชัน SUBSTITUTE
SUBSTITUTE	=SUBSTITUTE (text,old_text,new_text,instance_num)
	ฟังก์ชัน SUBSTITUTE ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

ตารางตัวอย่างการใช้ SUBSTITUTE

	A	B	C	D
1	Old Text	New Text	การใช้ฟังก์ชัน SUBSTITUTE	ผลลัพท์
2	Retail Sales	2013	=SUBSTITUTE(A2,A2,(A2&" "&B2))	Retail Sales 2013
3	Jan 2013	January	=SUBSTITUTE(A3,"Jan",B3)	January 2013
4	1/1/2012		=SUBSTITUTE(A4,2,3,1)	1/1/3012
5	1/1/2012		=SUBSTITUTE(A4,2,3,2)	1/1/2013

อธิบายตารางตัวอย่างการใช้ SUBSTITUTE

1. ต่อท้าย Retail Sales ด้วย 2013 โดยใช้ &" "& เชื่อมคำและเว้นระหว่าง "" ก็เพื่อให้มี เคาะ 1 เคาะ ก่อน 2013
2. แทนที่ Jan ด้วย January
3. เมื่อระบุ instant_num เป็น 1 ฟังก์ชันจะแทนที่เลข 2 ตัวแรก ผลลัพท์จึงเป็น 1/1/3012
4. เปลี่ยน instant_num เป็น 2 ฟังก์ชันแทนที่เลข 2 ตัวที่สอง ผลลัพท์กลายเป็น 1/1/2013