如何将Excel数值单元格值转换成单词 [英] How to Convert Excel Numeric Cell Value into Words

问题描述

我正在使用 Microsoft Excel 2007 ，我的工作表中有一个数字列。

是否任何解决方案，我可以转换它的字等价？

我附加Excel Sheet的屏幕截图。

解决方案

在excel中没有内置的公式，您必须添加一个vb脚本并将其永久保存在MS中。 Excel的安装作为加载项。

1. 按Alt + F11


2. 菜单:(工具条）插入模块


3. 复制并粘贴以下代码

  Option Explicit 
 
公共号作为变量，十进制变量
 
 Sub SetNums（）
数字=数组（，一，二，三，四，五六七八十九 十一，十二，十三，十四，十五，十六，十七，十八，十八元， 二十，三十，四十，五十，六十，七十，八十， As Double）As String 
 Dim DecimalPosition As Integer，ValNo As Variant，StrNo As String 
 Dim NumStr As String，n As Integer，Temp1 As String，Temp2 As String 
'该宏已写入by Chris Mead  -  www.MeadInKent.co.uk 
如果Abs（MyNumber）> 999999999然后
 WordNum =值太大
退出函数
结束如果
 SetNums 
'金额（excl小数）的字符串表示
 NumStr =右（0，Val（Mid（NumStr，1,3）），Val（Mid（NumStr，1） 4，3）），Val（Mid（NumStr，7，3）））
对于n = 3到1步骤-1'将绝对数分析为3组3位数
 StrNo = Format ValNo（n），000）
如果ValNo（n）> 0然后
 Temp1 = GetTens（Val（Right（StrNo，2）））
如果Left（StrNo，1） 0then 
 Temp2 = Numbers（Val（Left（StrNo，1）））& 百
如果Temp1<> 然后Temp2 = Temp2& 和
 Else 
 Temp2 =
 End If 
如果n = 3然后
如果Temp2 =而ValNo（1）+ ValNo（2） > 0 Then Temp2 =and
 WordNum = Trim（Temp2& Temp1）
 End If 
如果n = 2则WordNum = Trim（Temp2& Temp1&1000& WordNum）
如果n = 1则WordNum = Trim（Temp2& Temp1&million& WordNum）
 End If 
 Next n 
 NumStr = Trim Abs（MyNumber）））
'小数位后的值
 DecimalPosition = InStr（NumStr，。）
数字（0）=零
如果DecimalPosition> 0和DecimalPosition Len（NumStr）然后
 Temp1 =point
对于n = DecimalPosition + 1 To Len（NumStr）
 Temp1 = Temp1& &数字（Val（Mid（NumStr，n，1）））
下一个n 
 WordNum = WordNum& Temp1 
 End If 
如果Len（WordNum）= 0或Left（WordNum，2）=p然后
 WordNum =Zero& WordNum 
 End If 
 End Function 
 
函数GetTens（TensNum As Integer）As String 
'将一个数字从0转换为99到文本。 
如果TensNum< = 19然后
 GetTens = Numbers（TensNum）
 Else 
 Dim MyNo As String 
 MyNo = Format（TensNum，00）
 GetTens = Tens（Val（Left（MyNo，1）））& &数字（Val（Right（MyNo，1）））
如果
结束函数
  

此后，从文件菜单中选择保存书，从下一个菜单中选择
Excel 97-2003加载项（* .xla）

它将保存为Excel加载项，直到安装到该机器的Ms.Office安装。

现在打开任何单元格类型中的任何Excel文件 = WordNum（<你的数值或单元格引用>）

你会看到一个等价于数值的单词。 p>

这段代码取自： http://en.kioskea.net/forum/affich-267274-how-to-convert-number-into-text-in-excel

I am working with Microsoft Excel 2007 and I have one Numeric Column in my Sheet.

Is there any solution that I can convert it's Words equivalent?

I am attaching Excel Sheet's Screen Shot.

解决方案

There is no built-in formula in excel, you have to add a vb script and permanently save it with your MS. Excel's installation as Add-In.

1. press Alt+F11
2. MENU: (Tool Strip) Insert Module
3. copy and paste the below code

Option Explicit

Public Numbers As Variant, Tens As Variant

Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
    Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
    Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
    ' This macro was written by Chris Mead - www.MeadInKent.co.uk
    If Abs(MyNumber) > 999999999 Then
        WordNum = "Value too large"
        Exit Function
    End If
    SetNums
    ' String representation of amount (excl decimals)
    NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
    ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
    For n = 3 To 1 Step -1    'analyse the absolute number as 3 sets of 3 digits
        StrNo = Format(ValNo(n), "000")
        If ValNo(n) > 0 Then
            Temp1 = GetTens(Val(Right(StrNo, 2)))
            If Left(StrNo, 1) <> "0" Then
                Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
                If Temp1 <> "" Then Temp2 = Temp2 & " and "
            Else
                Temp2 = ""
            End If
            If n = 3 Then
                If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
                WordNum = Trim(Temp2 & Temp1)
            End If
            If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
            If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
        End If
    Next n
    NumStr = Trim(Str(Abs(MyNumber)))
    ' Values after the decimal place
    DecimalPosition = InStr(NumStr, ".")
    Numbers(0) = "Zero"
    If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
        Temp1 = " point"
        For n = DecimalPosition + 1 To Len(NumStr)
            Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
        Next n
        WordNum = WordNum & Temp1
    End If
    If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
        WordNum = "Zero" & WordNum
    End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
    If TensNum <= 19 Then
        GetTens = Numbers(TensNum)
    Else
        Dim MyNo As String
        MyNo = Format(TensNum, "00")
        GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
    End If
End Function

After this, From File Menu select Save Book ,from next menu select "Excel 97-2003 Add-In (*.xla)

It will save as Excel Add-In. that will be available till the Ms.Office Installation to that machine.

Now Open any Excel File in any Cell type =WordNum(<your numeric value or cell reference>)

you will see a Words equivalent of the numeric value.

This Snippet of code is taken from: http://en.kioskea.net/forum/affich-267274-how-to-convert-number-into-text-in-excel

这篇关于如何将Excel数值单元格值转换成单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！