เปลี่ยนตัวเลขเป็นตัวอักษร ภาษาอังกฤษ ใน Excel

เปลี่ยนตัวเลขเป็นตัวอักษร ตอนที่เราพิมพ์ตัวเลข เช่น 1,200 บาท จะให้เป็นตัวอักษรอย่างเช่น หนึ่งพันสองร้อยบาทถ้วน ใน Excel เราใช้ bathtext ได้สบาย ง่าย ๆ ใช่มั้ย แต่ถ้าจะให้เป็นภาษาอังกฤษ คือ One Thousand and two hundred baht แบบนี้จะทำอย่างไร? ในเมื่อมันไม่มีฟังก์ชัน dollartext

ถ้าเป็นเงินบาท มันมีฟังก์ชัน BahtText ที่เราแค่เอาไปใช้งานได้เลย แบบนี้

เปลี่ยนตัวเลขเป็นตัวอักษร
ฟังก์ชัน bahttext ใน Excel

แต่มันเป็นภาษาไทย และมีแต่ภาษาไทยเท่านั้น ไม่มีภาษาอังกฤษ หรือ จีน หรือ อินเดีย หรือ ญี่ปุ่น หรือ ฟินแลนด์

เอ๊ะ หรือว่ามี? อันนี้พยายามหา dollartext หรืออะไรอื่นแล้วไม่เจอนะ

อันนี้ก็งง ๆ เหมือนว่าเป็นอภิสิทธิ์ของเราชาวไทยที่มีฟังก์ชันซึ่งไม่น่าจะมีประโยชน์กับคนชาติอื่นเท่าไหร่

แล้วฝรั่งเขาไม่ใช้อะไรแบบนี้เลยหรืออย่างไร? ก็เลยค้นกูเกิลดู พบว่าในเว็บไซต์ ไมโครซอฟต์ ได้เชียน VBA เพื่อเปลี่ยนตัวเลขเป็นตัวอักษรไว้แล้ว ย้าฮู้ วู้ปี้ กริ๊บกิ้ววววววววววววว

ซึ่งในเว็บไมโครซอฟต์ ตามลิงก์นี้ https://support.microsoft.com/en-gb/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98 ได้ให้สูตรเอาไว้

เราก็ copy ไปใช้เลยจ้ะ

ขออนุญาตไม่ copy มาให้ดูตรงนี้ เพราะได้ลองแล้วเจอบั๊กแฮะ

เปลี่ยนตัวเลขให้เป็นตัวอักษร - Debug
บั๊กที่พบจากสูตรของเว็บไมโครซอฟต์

อันนี้แปลกใจว่าเป็นเพราะอะไร ดูแล้วก็ครอบคลุมเวอร์ชันที่ใช้อยู่ (ด้านบนจะเขียนว่า Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010)

ในเมื่อมีบั๊ก เราก็ต้องแก้บั๊กกัน

Create custom functions

เราอาจจะคุ้นเคยกับการใช้ฟังก์ชันต่าง ๆ ใน Excel อย่างเช่น xlookup sumif หรืออะไรพวกนี้ ความจริง ฟังก์ชันเหล่านี้ก็คือการเขียนสูตรยาว ๆ แล้วเก็บเป็นโมดูลให้เราเรียกใช้ง่าย ๆ ซึ่ง เราก็สามารถเขียนฟังก์ชันขึ้นมาเองก็ได้

วิธีคือ กดเข้าไปที่แท็บ Developer แล้วไปที่ Visual Basic แล้วกด Insert เลือก Module ขึ้นมา

(หรือกด Alt + F11 ก็ได้ ง่าย ๆ เร็วดี)

หมายเหตุ หากว่าท่านไม่มีแท็บ Developer ให้ไปที่แท็บ File เลือก Options แล้วเลือก Customize Ribbon.

หน้าต่าง Customize the Ribbon ให้ดูที่ Main หาคำว่า Developer ติ๊กถูก คลิกโอเค

—–

พอเปิด Visual Basic Editor ขึ้นมาแล้ว ก็พิมพ์สูตรที่เราต้องการลงไป

ข้อเสียคือ สิ่งที่เราเขียนเพิ่มเติมนี้ เป็น Macro ถ้าเราจะบันทึกไฟล์ Excel เราต้องบันทึกเป็น Excel Macro-Enabled Workbook หรือ .xlsm นะจ๊ะ ถ้าเราบันทึกเป็น xlsx จะไม่เก็บ VBA ที่เราเขียน

การใช้งาน

วิธีการใช้งานก็เหมือนกับฟังก์ชันทั่วไป คือ เรามาใส่ = ตามด้วยชื่อฟังก์ชัน

เช่น = SpellNumber (D3)

ซึ่งพอกดแล้ว อ้าว error ต้องไป debug

เปลี่ยนตัวเลขเป็นตัวอักษร ในโค้ดของไมโครซอฟต์มีอะไรบ้าง?

ในฟังก์ชันนี้ (ซึ่งไมโครซอฟต์ใช้ชื่อฟังก์ชัน SpellNumber โดยในช่วงแรกจะมีการจัดการกับทศนิยม คือ ต้องกำหนดก่อนว่า ตรงที่เป็นทศนิยม จะเป็น Cent หรือ Cents กับตรงตัวจำนวนเต็มจะเป็น dollar หรือ dollars โดยถ้าเป็น 0 จะเรียกว่า no cent หรือ no dollar

และจะมี ฟังก์ชันย่อยอีกคือ

Function GetHundreds

Function GetTens

Function GetDigit

ศึกษา สูตรเปลี่ยนตัวเลขเป็นตัวอักษร ที่เขาให้มา ไล่ดูว่าวิธีการคิดของเขาจะมีอะไรบ้าง เวลาที่ Excel ส่งไปหน้า Visual Basic Editor นี่ จะไปไฮไลต์ตรงที่มีปัญหาให้ทันที โดยเราสามารถกดปุ่มเพลย์ (ที่เป็นสามเหลี่ยมอยู่ตรง toolbar) หรือจะกด F5 ก็ได้ เมื่อได้ลองกด และเช็คเส้นทางแล้ว ก็เลยคิดว่าบางอย่างน่าจะบายพาสต์ไปได้ อย่างเช่นตรงที่แรกที่มีปัญหา นั้น ชี้ไปที่โค้ดซึ่งมีการสร้าง temp ขึ้นมาก่อน

Function GetDigit

GetDigit เป็นแค่การกำหนดว่า 1 = One อะไรพวกนี้ ไม่มีอะไรซับซ้อน

จะเป็นการเปลี่ยนตัวเลขเป็นตัวอักษร นั่นคือ

1 = One

2 = Two

3 = Three

4 = Four

5 = Five

6 = Six

7 = Seven

8 = Eight

9 = Nine

Function GetTens

ฟังก์ชันต่อมา GetTens จะเป็นการเปลี่ยนตัวเลขเป็นตัวอักษรกับหลักสิบ หรือระหว่าง 10 – 99 ซึ่งในภาษาอังกฤษ ค่าตั้งแต่ 10 ถึง 19 จะมีคำเรียกเฉพาะ โค้ดของไมโครซอฟต์นี้จะแยกเป็น 2 กรณี โดยตั้งตัวเลือกไว้ว่า ให้ตัดตัวซ้าย (left) ถ้าตัวหน้า เป็นเลข 1 ให้ทำ case 10 ถึง 19 แต่ถ้าตัวนำหน้าไม่ใช่เลข 1 ก็กำหนดเลยว่า ถ้านำหน้าด้วย 2 ให้แปลงเป็น twenty อะไรทำนองนั้น

กลุ่มแรก ถ้าตัวหน้า (ของหลักสิบ) เป็นเลข 1

10 =Ten

11 = Eleven

12 = Twelve

13 = “hirteen

14 = Fourteen

15 = Fifteen

16 = Sixteen

17 = Seventeen

18 = Eighteen

19 = Nineteen

ถ้าตัวหน้า (ของหลักสิบ) ไม่ใช่ 1 ให้ดูตามนี้

2 = Twenty

3 = Thirty

4 = Forty

5 = Fifty

6 = Sixty

7 = Seventy

8 = Eighty

9 = Ninety

Function GetHundreds

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

หลักแสน เรียกว่า hundred Thousand หลักล้าน เรียกว่า Million หลักพันล้าน เรียกว่า Billion หลักล้านล้าน เรียกว่า Trillion หลัก เอ่อ เรียกหลักอะไร ถ้ามีศูนย์ต่อท้าย 15 ตัว จะเรียกว่า  Quadrillion มีศูนย์ต่อท้าย 18 ตัว เรียกว่า Quintillion ไปจนถึง เลขศูนย์ต่อท้าย 303 ตัว เรียกว่า Centillion

แต่เราทำถึงหลักล้านล้านก็แล้วกัน เกินจากนี้โอกาสใช้งานคงน้อยมากมากมากมากมากกกกกกกกกกกกกกก (เติม ก ไก่ หมดโลก)

แทรกเป็นเกร็ดนิดนึง เมื่อก่อน billion จะเป็นหลัก ล้านล้าน หรือ 1,000,000,000,000 แต่พอสหรัฐครองโลก ก็ปรับการนับ billion เป็นแค่ พันล้าน 1,000,000,000 ส่วนที่เป็นล้านล้าน จะเป็น Trillion

คร่าว ๆ ก็ประมาณนี้

Code ที่แก้ไข

จาก debug ที่ขึ้นว่ามีปัญหา คือ

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2))

ตรงนี้คือการจัดการเรื่อง cents (ในทศนิยม) และจัดการปรับ MyNumber (ตัวเลขที่ใส่) ให้เป็น dollar เมื่อเห็นบรรทัดนี้ สิ่งแรกที่สงสัยคือ & _ ตรงขีดล่างนั้นมาจากไหนใช้ทำอะไร? เพราะคำสั่งนี้ไม่น่ามีปัญหา การปรับทศนิยม 2 ตำแหน่ง

เมื่อนั่งไล่ดูโค้ด ก็รู้สึกว่า เอ๊ะ ทำไมโค้ดนี้สร้าง Temp ขึ้นมาก่อน มันจำเป็นต้องสร้าง Temp หรือเปล่า?

ดังนั้น โค้ดบรรทัดแรกคือ

Dim Dollars, Cents, Temp

ถ้าเราจะตัด Temp ออก แล้ว Temp ไว้ทำอะไร ? เลื่อนไปดูด้านล่าง จะเห็นโค้ดว่า

Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars

นั่นคือเป็นตัวที่เก็บข้อมูลของตัวดอลลาร์ หรือ จำนวนเต็ม

ก็เลยคิดว่า ถ้าตัดตรง Temp ออกเลย ไม่ต้องไปสนใจ แล้วเปลี่ยนให้ dollar เป็นตัวแทนเลย

Dollars = GetHundreds & Temp (Count) & Dollars

โดยกำหนดใหม่เลยว่า Temp เป็น อาร์เรย์ ของค่า Thousand  Million

เมื่อไล่แก้แล้ว เปลี่ยนตัวเลขเป็นตัวอักษร โดยอยู่บนพื้นโค้ดของไมโครซอฟต์ก็จะได้เป็น โค้ดใหม่ ดังนี้

Function SpellNumber(ByVal MyNumber)
'Debug by DataRevol
Dim Dollars, Cents
Temp = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
MyNumber = Trim(Str(MyNumber))
' จัดการกับทศนิยม
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
    GetHundreds = ""
    GetValue = Right(MyNumber, 3)
    If Val(GetValue) <> 0 Then
        GetValue = Right("000" & GetValue, 3)
        If Mid(GetValue, 1, 1) <> "0" Then
            GetHundreds = GetDigit(Mid(GetValue, 1, 1)) & " Hundred "
        End If
        If Mid(GetValue, 2, 1) <> "0" Then
            GetHundreds = GetHundreds & GetTens(Mid(GetValue, 2))
        Else
            GetHundreds = GetHundreds & GetDigit(Mid(GetValue, 3))
        End If
    End If
    If GetHundreds <> "" Then
        Dollars = GetHundreds & Temp(Count) & Dollars
    End If
    If Len(MyNumber) > 3 Then
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
        MyNumber = ""
    End If
    Count = Count + 1
Loop
Select Case Dollars
    Case ""
        Dollars = "No Dollars"
    Case "One"
        Dollars = "One Dollar"
    Case Else
        Dollars = Dollars & " Dollars"
End Select
Select Case Cents
    Case ""
        Cents = " and No Cents"
    Case "One"
        Cents = " and One Cent"
    Case Else
        Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function

' แปลงเลขในช่วง 10 ถึง 99 เป็นตัวหนังสือ
Function GetTens(TensText)
Dim Result As String
Result = "" ' ตั้งค่า Temp จากสูตรเดิม
If Val(Left(TensText, 1)) = 1 Then ' ตรงนี้คือการกำหนด ถ้านำหน้าด้วย 1 หรือ 10-19
    Select Case Val(TensText)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
    End Select
Else
Select Case Val(Left(TensText, 1))' ตรงนี้คือการกำหนด ถ้าเลขนำหน้าไม่ใช่ 1 
    Case 2: Result = "Twenty "
    Case 3: Result = "Thirty "
    Case 4: Result = "Forty "
    Case 5: Result = "Fifty "
    Case 6: Result = "Sixty "
    Case 7: Result = "Seventy "
    Case 8: Result = "Eighty "
    Case 9: Result = "Ninety "
    Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1))
End If
GetTens = Result
End Function
' เปลี่ยนเลขจาก 1 ถึง 9 เป็นตัวหนังสือ
Function GetDigit(Digit)
Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
End Select
End Function

ซึ่งก็ลองเอาไปเทสต์แล้ว ก็ใช้งานได้อยู่

เปลี่ยนตัวเลขเป็นตัวอักษร

ที่เขียนเรื่อง เปลี่ยนตัวเลขเป็นตัวอักษร นี้ก็เพื่อให้เห็นว่า บางทีถ้าเราอยากจะทำฟังก์ชันเป็นพิเศษใช้เองก็ได้เหมือนกัน

ความคิดเห็นของคุณ :)

%d bloggers like this: