วันพฤหัสบดีที่ 17 มกราคม พ.ศ. 2556

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

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

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


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


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

TRIM

=TRIM(text), text คือ ข้อความที่ต้องการเอาช่องว่างออก
using TRIM function in Excel
ฟังก์ชัน Trim จะเอาช่องว่างออกจากข้อความ ยกเว้นช่องว่างระหว่างคำ 1 เคาะ

CLEAN

=CLEAN(text), text คือ ข้อความที่ต้องการเอาอักขระที่พิมพ์ไม่ได้ออก
using CLEAN function in Excel
ฟังก์ชัน Clean จะตัดออกตัวอักขระ ASCII ซึ่งถูกกำหนดให้ใช้กับฟังก์ชั่น CHAR(0)-CHAR(31)

ตัวอย่างการใช้ TRIM และ CLEAN
ABCD
1ข้อมูล การใช้ฟังก์ชันผลลัพท์คำอธิบายการใช้ฟังก์ชัน TRIM, CLEAN
2 Year  2013  =TRIM(A2)Year 2013 ช่องว่างก่อนและหลัง Year ลดลง 1 ช่อง
3 HelloASCII character codes (Char2 and Char7) =CLEAN(A3) Hello ASCII character codes (Char2 and Char7) เป็นอักขระที่ไม่พิมพ์หายไป (ค่า ASCll = 2, 7)
Functionไวยากรณ์และการทำงานของฟังก์ชัน SUBSTITUTE

SUBSTITUTE

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

ตารางตัวอย่างการใช้ SUBSTITUTE
ABCD
1 Old Text New Textการใช้ฟังก์ชัน SUBSTITUTE ผลลัพท์
2Retail 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

Reactions:

2 ความคิดเห็น:

  1. ไม่ระบุชื่อ2/06/2559 9:58 ก่อนเที่ยง

    A1 มีข้อความดังนี้ 70000ป56214ป258 ต้องการให้ A2 แสดงค่าที่อยู่ระหว่าง ป.ปลา ใช้สูตรไหนครับ

    ตอบลบ
    คำตอบ
    1. ตอบที่ หัวข้อ แยกตัวเลข,ข้อความในคอลัมภ์เดียวให้เป็นสองคอลัมภ์

      ลบ