co-create
บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


 

ก่อนจะนำข้อมูลไปสรุปผลในบทต่อๆ ไปได้ คุณอาจจำเป็นต้องจัดการข้อมูล ให้ออกมาอยู่ในรูปแบบที่คุณต้องการก่อน เพื่อให้สามารถสรุปข้อมูลได้มีประสิทธิภาพมากขึ้น เช่น แยกชื่อและนามสกุลออกจากกัน หรือมีการแปลงคำบางอย่าง เช่น แปลงตัวพิมพ์เล็กพิมพ์ใหญ่

หนึ่งในฟังก์ชั่นที่มีบทบาทในการแปลงข้อมูลหรือจัดการข้อมูลให้เรียบร้อยมากขึ้นมากที่สุดตัวหนึ่งก็คือ ฟังก์ชั่นกลุ่ม TEXT นั่นเอง ซึ่งคุณจะต้องรู้จักฟังก์ชั่นพื้นฐานเหล่านี้ก่อนครับ คุณอาจต้องใช้มันผสมผสานกันพอสมควรเลยล่ะ

สรุปฟังก์ชั่นกลุ่ม Text ที่ใช้บ่อย

LEN

นับจำนวนตัวอักษร (นับทั้งอักษร ช่องว่าง สระ และวรรณยุกต์ด้วย)

len

=LEN(text)
=LEN(ข้อความที่จะนับอักษร)
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =LEN(“มดแดง”) =5

LEFT

ตัดคำจากทางซ้าย (ระบุได้ว่าตัดกี่ตัวอักษร)

left

=LEFT(text,

[num_chars])
=LEFT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =LEFT(“มดแดง”,2) =“มด”

MID

ตัดคำโดยระบุได้ว่าเริ่มตัดที่ตำแหน่งไหน เป็นจำนวนกี่ตัวอักษร

mid-fix

=MID(text,start_num,num_chars)
=MID(ข้อความต้นฉบับ,อักษรที่เริ่มตัด,จำนวนอักษรที่จะตัด)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =MID(“มดแดง”,3,2) =“แด”

RIGHT

ตัดคำจากทางขวา (ระบุได้ว่าตัดกี่ตัวอักษร)

right
=RIGHT(text, [num_chars])
=RIGHT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =RIGHT(“มดแดง”,2) =“ดง”

TRIM

ตัดช่องว่างส่วนเกินออก (ลบช่องว่างที่อยู่หน้า หลัง รวมถึงช่องว่างตรงกลางที่เกิน 1 เคาะ)

trim
=TRIM(text)
=TRIM(ข้อความที่จะตัด)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =TRIM(”  inw  excel “) =”inw excel” (เหลือช่องว่างกลาง 1 space)

FIND 

ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด (สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่

find
=FIND(find_text, within_text, [start_num])
=FIND(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =FIND(“Excel”,”inwexcel is Excellent”) =13

SEARCH

ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด โดยใช้การหาแบบ Wildcard (ไม่สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่

search
=SEARCH(find_text,within_text,[start_num])
=SEARCH(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
ผลลัพธ์ : เป็น Number
ตัวอย่าง : =SEARCH(“Excel”,”inwexcel is Excellent”) =4

SUBSTITUTE

แทนที่คำที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้คำที่จะถูกแทนที่

substitute
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE(คำที่ต้องการแทนที่, ส่วนของคำเดิมที่จะถูกแทนที่, ส่วนของคำใหม่ที่จะมาแทนที่, [เจาะจงอันที่ถูกแทนที่ได้])
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =SUBSTITUTE(“ผม like มาก”,”like”,”ชอบ”) = “ผม ชอบ มาก”

REPLACE

แทนที่ตำแหน่งที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้ตำแหน่งและจำนวนตัวอักษรที่จะถูกแทนที่

replace
=REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE(คำที่ต้องการแทนที่, ตำแหน่งเริ่มที่จะแทนที่, จำนวนอักษรที่จะแทนที่, คำใหม่ที่จะมาแทนที่)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =REPLACE(“081-234-5678”,5,3, “ไม่บอก”) = “081-ไม่บอก-5678”

TEXT

เปลี่ยนรูปแบบ Number Format โดยใช้สูตร

text
=TEXT(value, format_text)
ในช่อง format_text ให้ใส่รูปแบบคล้ายการทำ Custom Format ซึ่งจะอธิบายต่อไปในบทหลัง
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =TEXT(1234.5678,”0.00″)= “1234.57”

UPPER

แปลงข้อความให้กลายเป็นตัวพิมพ์ใหญ่ทั้งหมด

upper
= UPPER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =UPPER(“mOnKey”)= “MONKEY”

LOWER

แปลงข้อความให้กลายเป็นตัวพิมพ์เล็กทั้งหมด

lower
= LOWER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =LOWER(“mOnKey”)= “monkey”

PROPER

แปลงข้อความแต่ละคำให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่ นอกนั้นตัวพิมพ์เล็กทั้งหมด

proper
= PROPER(text)
ผลลัพธ์ : เป็น Text
ตัวอย่าง : =PROPER(“mOnKey”)= “Monkey”

ตัวอย่างการประยุกต์

ตัวอย่าง1 : แยกชื่อและนามสกุล

split-name

เรามาลองดูกันว่าถ้า A1 มีชื่อคนเขียนว่า “สมเทพ จริงนะ” อยู่ หากเราจะเอาคำว่า “สมเทพ” และ “จริงนะ” ออกมาไว้แต่ละช่องแยกกันด้วยวิธีการเขียนสูตร เราจะต้องเขียนสูตรว่าอย่างไร

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

ขั้นตอนการคิด Function หลัก วิธีการเขียนสูตร
รู้ว่าชื่อและนามสกุลแยกกันด้วยการเว้นวรรค เครื่องหมาย
เว้นวรรค
เครื่องหมายเว้นวรรค แทนด้วย ” “
ค้นหาเครื่องหมายเว้นวรรค FIND =FIND(” “,A1) จะได้ผลเป็นเลข 6
เอาทุกอย่างที่อยู่หน้า (ทางซ้าย) ของเว้นวรรคไปใส่ในช่อง ชื่อ LEFT ใช้ LEFT ไปจนถึงตำแหน่งของการเว้นวรรค แต่ลบด้วย1 เพราะไม่ต้องการเครื่องหมายเว้นวรรคติดมาด้วย =LEFT(A1, FIND(” “,A1)-1)
เอาทุกอย่างที่อยู่หลัง (ทางขวา) ของเว้นวรรคไปใส่ในช่อง นามสกุล RIGHTLEN จะใช้ RIGHT แต่ต้องหาว่าต้องนับจากทางขวากี่ตัวอักษร วิธีการหา คือ เอาความยาวคำทั้งหมด ลบด้วยตำแหน่งเครื่องหมายเว้นวรรค
=LEN(A1)-FIND(” “,A1)จากนั้นพอเอามาใส่ใน RIGHT จะได้ว่า
=RIGHT(A1, LEN(A1)-FIND(” “,A1))

ถ้าถามว่าแล้วถ้ามีเว้นวรรค 2 ที เช่น มีคำนำหน้า ชื่อ และนามสกุล แบบนี้จะแยกออกมาเป็น 3 คอลัมน์ยังไง?

ถ้าให้ง่ายที่สุด ผมก็แนะนำให้ทำแบบนี้ 2 รอบครับ รอบแรกจะแยก space ตัวแรกสุดได้ เหลือเป็น นาย สมเทพ จริงนะ ส่วนรอบ 2 จะแยก space ตัวที่สองได้ เป็น นาย สมเทพ จริงนะ

ตัวอย่าง2 : การแยกคำที่ช่องว่างตัวที่สอง

ถ้า A1 มีชื่อคนเขียนว่า “สมเทพ จริงนะ เชื่อสิ” อยู่ หากเราจะเอาคำว่า “สมเทพ จริงนะ” และ “เชื่อสิ” ออกมาไว้แต่ละช่องแยกกันด้วยวิธีการเขียนสูตร เราจะต้องเขียนสูตรว่าอย่างไร

split-name2

วิธีคิด : ในตัวอย่างนี้ จะใช้สูตร FIND หาช่องว่างตรงๆ ไม่ได้ เพราะ FIND จะเจอช่องว่างตัวแรกเสมอ ที่นี้ผมมีวิธีแก้ไข 2 วิธีมาแนะนำ คือ

วิธีที่ 1 :

ใช้ FIND เหมือนเดิม แต่ระบุ Argument =FIND(find_text,within_text,[start_num]) ให้ไปเริ่มหลังจาก Space ตัวแรกที่หาเจอ

separate2

  • Step 1 : หา space ตัวแรกด้วย Find ตามปกติ จาก =FIND(” “,A1) จะได้ผลลัพธ์เป็นเลข 6 ทดไว้ที่ A2
  • Step 2 :เอาค่าที่ได้จาก step 1 บวกเพิ่มไป 1 ตัวแล้วให้เป็น start_num ของ find อีกตัว จะได้ว่า =FIND(” “,A1,A2+1) จะได้ผลเป็นเลข 13 ซึ่งคือตำแหน่ง space ตัวที่สอง ทดไว้ที่ A3
  • Step 3 :ใช้LEFTกับRIGHTตัดคำ คล้ายตัวอย่างที่แล้ว
    • =LEFT(A1, A3 -1)
    • =RIGHT(A1, LEN(A1)- A3)

วิธีที่ 2 :

ใช้ SUBSTITUTE แทนที่ Space ตัวที่สองด้วยอักขระแปลกๆ แล้วค่อยตัดคำจากอักขระนั้น

separate3

  • Step 1 : ใช้ SUBSTITUTE แทนที่ Space ตัวที่สอง โดยการระบุ Argument
    =SUBSTITUTE(text,old_text,new_text,[instance_num]) เป็น 2
    จะได้ว่า =SUBSTITUTE(A1,” “,”|”,2) จะได้ผลลัพธ์เป็น สมเทพ จริงนะ|เชื่อสิ ทดไว้ที่ A2
  • Step 2 : หาคำแหน่งของอักขระ | ด้วย FIND
    ได้ว่า =FIND(“|”,A2) จะได้ผลลัพธ์เป็นตำแหน่งที่ 13 ทดไว้ที่ A3
  • Step 3 : จากนั้นก็ใช้ LEFT กับ RIGHT ตัดคำเหมือนวิธีที่แล้ว

ตัวอย่าง3 : การหาว่ามีอักขระที่สนใจอยู่กี่ตัวในคำที่ต้องการ

หากผมต้องการหาว่าในคำที่ผมต้องการ เช่น baseball มีตัว b อยู่กี่ตัว? ผมจะเขียนสูตรยังไงดี

แนวคิด คือ ให้นับจำนวนตัวอักษรทั้งหมดไว้ก่อน จากนั้นให้แทนที่ b ด้วย blank แล้วนับจำนวนอักษรอีกที แล้วค่อยมาลบกัน

สมมติ คำที่ผมสนใจอยู่ใน A1

sub-example

  • Step 1 : นับจำนวนตัวอักษรทั้งหมด
    =LEN(A1)
  • Step 2 : แทนที่ b ด้วย blank
    จะได้ว่า =SUBSTITUTE(A1,”b”,””) จะได้ผลลัพธ์เป็น aseall ผมเก็บไว้ใน A3
  • Step 3 : นับจำนวนตัวอักษรทั้งหมด หลังจากตัด b ทิ้งแล้ว
    =LEN(A3)
  • Step 4 : หาผลต่างของจำนวนอักษร
    =LEN(A1)-LEN(A3)  นั่นเอง