WPS表格中带小数数字转英文大写教程

大家好，欢迎来到IT知识分享网。

这里使用WPS做示例。

首先个人的WPS需要先启用宏

第一步先切换到工具选项卡，选择开发工具；

点击VB编辑器，会弹出下载插件提示

根据提示下载好插件，先关闭WPS并安装好插件，再重新打开WPS的VB编辑器，出现如下界面

说明VBA宏插件正常运行

以上过程也可参考下面文章自行下载插件

二、打开WPS的VB编辑器后

按一下 [插入] 索引標籤，再按一下 [模組]。

複製下列程式碼行。

Function SpellNumber(ByVal MyNumber) Dim Dollars, Temp Dim DecimalPlace, Count ReDim Place(9) As String Application.Volatile True Place(2) = " THOUSAND " Place(3) = " MILLION " Place(4) = " BILLION " Place(5) = " 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 <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "" Case "One" Dollars = "ONE DOLLAR" Case Else Dollars = "SAY TOTAL U.S. DOLLARS " & Dollars End Select Select Case Cents Case "" Cents = " ONLY" Case "One" Cents = " AND ONE CENT" Case Else Cents = " AND " & Cents & " CENTS" End Select SpellNumber = Dollars & Cents End Function Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " HUNDRED " End If If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function Function GetTens(TensText) Dim Result As String Result = "" If Val(Left(TensText, 1)) = 1 Then 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)) 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 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 

將上述程式碼行貼入 [模組1 (程式碼)] 方塊中。

在個別儲存格中使用 SpellNumber 函數

1. 將公式 =SpellNumber (A1) 輸入您要顯示書面數位的單元格，其中 A1 是包含您要轉換之數位的單元格。 您也可以手動輸入像是 =SpellNumber (22.50) 值。

2. 按 Enter 以確認公式。

三、可根据实际需要修改函数

如代码中”SAY TOTAL U.S. DOLLARS “可按需改为”US DOLLARS”，”ONLY”改为”AND NO CENTS”，Result = “数字”数字前加连接词”AND ” 等等，这里不再展开，可以看看下面参考文章~

参考文章：將數字轉換成文字 – Microsoft Support

四、在以后的新文档中使用该函数

我们大费周章将函数设置好当然不是为了单次使用，下面讲讲如何将其作为如同excel预设函数般，在新打开的文档也能直接调用

1.新建excel表格，按上述步骤VB插入模块粘贴代码

2.将文件另存为加载宏文件，放在你的excel库里不要删它

3.新打开一个excel表格，在开发工具里选择加载项

点击浏览，选择并导入刚才保存的文件即可

以上就是关于excel数字转英文大写的全部分享，觉得有帮助就点点赞扩散给更多人吧~