Text Functions

Text Functions คือกลุ่มฟังก์ชั่นที่ใช้ในการจัดการข้อความ เช่นการตัดคำ การนับ การเปลี่ยนแทนค่าเดิมเป็นค่าใหม่ การนับจำนวนอักขระ การแสดงเป็นอักษรตัวใหญ่ แสดงเป็นอักษรตัวเล็ก การ Clean หรือลบอักขระที่เกินจำเป็นเช่นค่าที่ไม่สามารถแสดงทาง เครื่องพิมพ์ทิ้งไป เป็นต้น ซึ่งเป็นประโยชน์อย่างมากกับการจัดการข้อมูลให้เป็นไปตามรูปแบบ ตามค่าที่ต้องการ

Video ด้านล่างนี้อธิบายความหมายและสาธิตการใช้งานฟังก์ชั่นในหมวด Text ซึ่งเป็นฟังก์ชั่นที่ใช้บ่อยครั้งในการทำงานกับ Excel จึงควรศึกษาให้เข้าใจ ใช้ให้เป็น ประโยชน์ที่สำคัญยิ่งอีกประการคือ ฟังก์ชั่นเหล่านี้จะเป็นพื้นฐานในการประยุกต์ใช้งานที่ซับซ้อนในลำดับสูง ๆ ขึ้นไป

Video แสดงการใช้งาน Text Functions

สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

การแกะสูตรการหาจำนวนอักขระจากข้อความ

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

ยกตัวอย่างเช่น เราจะหาว่าตัว W ปรากฏกี่ครั้งในข้อความด้านล่างนี้ซึ่งอยู่ที่เซลล์ A1

When Where What Who How

ซึ่งหากนับด้วยตาเราก็พบว่า 5 อักขระ ใครที่คิดว่าตนเองมีความชำนาญในการใช้ฟังก์ชั่น ลองคิดหาวิธีก่อนที่ผมจะเฉลยดูครับ

ฟังก์ชั่นที่ใช้ในการหาจำนวนครั้งที่ปรากฏของ W คือที่ B1 คีย์

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

หลักการคิดคือนำจำนวนอักขระทั้งหมดหักด้วยจำนวนอักขระคงเหลือหลังจากลบอักขระที่ต้องการหาออกไปแล้ว

การแกะสูตร

จากสูตร =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

1. Len(A1) เป็นการนับว่าอักขระทั้งหมดมีกี่อักขระ คำตอบคือ 23
2. Upper(A1) เป็นการแปลงให้เซลล์ A1 เป็นตัวใหญ่ทั้งหมด การแกะสูตรให้นำเมาส์ไปคลุม Upper(A1) ที่ Formula bar แล้วกดแป้น F9 จะได้เป็น "WHAT WHERE WHEN WHO HOW"
3. จากข้อ 2 จะได้เป็น Substitute("WHAT WHERE WHEN WHO HOW","W","") ซึ่งหมายความว่าให้เปลี่ยน W ที่ปรากฎในข้อความให้เป็นค่าว่าง

หากลากเมาส์คลุม Substitute("WHAT WHERE WHEN WHO HOW","W","") แล้วกดแป้น F9 จะได้เป็น

LEN("HAT HERE HEN HO HO")

จะเห็นว่าตัว W หายไป ผลลัพธ์จากฟังก์ชั่น LEN("HAT HERE HEN HO HO") จะได้ค่า 18

เมื่อนำ 23 จากข้อ 1 มาหักจาก 18 ตามข้อ 3 จะได้ 5 ซึ่งเป็นคำตอบตรงกับที่นับด้วยตา

คำถาม ทำไมต้องใช้ฟังก์ชั่น Upper() มาช่วย

คำตอบ เนื่องจากโจทย์นี้มีทั้ง W และ w สองตัวนี้มีค่าไม่เทียบเท่ากันในฟังก์ชั่น Substitute จึงจำเป็นต้องแปลงให้เป็นตัวเล็กหรือตัวใหญ่เสียก่อน ซึ่งผมแปลงให้เป็นตัวใหญ่ด้วยฟังก์ชั่น Upper

การเปลี่ยนตัวอักษรในภาษาอังกฤษให้เป็นตัวเล็ก, ใหญ่ หรือ ขึ้นต้นด้วยตัวใหญ่ (VBA)

สำหรับการเปลี่ยนตัวอักษรภาษาอังกฤษจากตัวเล็กเป็นตัวใหญ่ หรือตรงกันข้ามหรือเปลี่ยนจากคำนำหน้าให้เป็นตัวใหญ่ โดยปกติสามารถใช้ฟังก์ชั่น Upper(), Lower() หรือ Proper() ได้

แต่หากข้อมูลที่ได้รับมากระจัดกระจายในตำแหน่งที่ไม่แน่นอน จะทำให้ใช้ฟังก์ชั่นในการจัดการได้ยาก หากใช้ VBA มาจัดการแทนค่อนข้างจะง่ายกว่า ซึ่งตัวอย่างการใช้ VBA ในการจัดการข้อมูลสามารถดูได้ตามด้านล่างครับ

A. การเปลี่ยนตัวอักษรให้เป็นตัวเล็กทั้งไฟล์

Sub ChagetoLower()
    Dim r As Range
    Dim i As Integer
    For i = 1 To Worksheets.Count
      For Each r In Worksheets(i).UsedRange
        r = LCase(r)
      Next r
    Next i
End Sub

B. การเปลี่ยนตัวอักษรให้เป็นตัวใหญ่ทั้งไฟล์

Sub ChagetoUpper()
    Dim r As Range
    Dim i As Integer
    For i = 1 To Worksheets.Count
      For Each r In Worksheets(i).UsedRange
        r = UCase(r)
      Next r
    Next i
End Sub

C. การเปลี่ยนให้ตัวอักษรขึ้นต้นแต่ละคำด้วยตัวใหญ่ทั้งไฟล์

Sub ChagetoProper()
    Dim r As Range
    Dim i As Integer
    For i = 1 To Worksheets.Count
      For Each r In Worksheets(i).UsedRange
        r = Application.WorksheetFunction.Proper(r)
      Next r
    Next i
End Sub