EXCEL表格中将数字金额转换为英文

第一篇：EXCEL表格中将数字金额转换为英文

Excel表格中如何将数字金额转换为英文 （如B1列写162890元，自动转换为英文

ONE HUNDRED SIXTY TWO THOUSAND EIGHT HUNDRED NINETY DOLLARS AND NO CENTS）

1、新建Excel表格

2、按住“Alt+F11”打开VBA编辑器

3、在VBA编辑器中单击菜单栏“插入”——模块

4、在打开的模块中输入如下代码： Option Explicit Function 数字转英文(ByValMyNumber)Dim Dollars, Cents, Temp Dim DecimalPlace, Place(5)=

Count “

ReDimPlace(9)As Trillion

”

String

Place(2)=

=

“ Thousand ” Place(3)= “ Million ” Place(4)= “ Billion ”

MyNumber

Trim(Str(MyNumber))DecimalPlace = InStr(MyNumber, “.”)If DecimalPlace> 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1)& _

“00”,

2))MyNumber

=

Trim(Left(MyNumber,

DecimalPlace3)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 数字转英文 = Dollars & Cents End Function Function GetHundreds(ByValMyNumber)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

5/现在回到Excel表格中，单击“B1”单元格，在菜单栏选择“插入”——函数。6/在打开的“插入函数”对话框的“或选择类别”中选择“用户定义”，然后选择函数“数字转英文”，单击“确定”按钮。

7/在打开的“函数参数”对话框中输入“A1”，单击“确定”按钮。8/ 然后用填充手柄向下填充公式，现在就可以看到转换好的英文了。

第二篇：阿拉伯数字金额转换为英文会计金额

怎样用自定义函数将阿拉伯数字金额转换为英文会计金额，如：123.45 变为：One Hundred Twenty Three Dollars and Forty Five Cents

A：按Alt+F11，插入→模块→在VBE窗口中输入以下代码： 1.Function SpellNumber(ByValMyNumber)2.Dim Dollars, Cents, Temp 3.Dim DecimalPlace, Count 4.ReDim Place(9)As String 5.Application.Volatile True 6.Place(2)= “ Thousand ” 7.Place(3)= “ Million ” 8.Place(4)= “ Billion ” 9.Place(5)= “ Trillion ” ' String representation of amount 10.MyNumber = Trim(Str(MyNumber))' Position of decimal place 0 if none 11.DecimalPlace = InStr(MyNumber, “.”)12.'Convert cents and set MyNumber to dollar amount 13.If DecimalPlace> 0 Then 14.Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1)& “00”,

2))15.MyNumber

=

Trim(Left(MyNumber,

DecimalPlace3)23.Else 24.MyNumber = “\" 25.End If 26.Count = Count + 1 27.Loop 28.Select Case Dollars 29.Case ”“ 30.Dollars = ”No Dollars“ 31.Case ”One“ 32.Dollars = ”One Dollar“ 33.Case Else 34.Dollars = Dollars & ” Dollars“ 35.End Select 36.Select Case Cents 37.Case ”“ 38.Cents = ” and No Cents“ 39.Case ”One“ 40.Cents = ” and One Cent“ 41.Case Else 42.Cents = ” and “ & Cents & ” Cents“ 43.End Select 44.SpellNumber number

=

Dollars

&

Cents

45.End

Function

*

46.'******************************************* 47.' Converts a

from

100-999

into

text

48.'*******************************************

49.Function

GetHundreds(ByValMyNumber)50.Dim Result As String 51.If Val(MyNumber)= 0 Then Exit Function 52.MyNumber = Right(”000“ &MyNumber, 3)'Convert the hundreds place 53.If Mid(MyNumber,

1,

1)<>

”0“

Then

54.Result

=

GetDigit(Mid(MyNumber, 1, 1))& ” Hundred “ 55.End If 56.'Convert the tens and ones place 57.If Mid(MyNumber, 2, 1)<> ”0“ Then 58.Result = Result &GetTens(Mid(MyNumber, 2))59.Else

60.Result

=

Result

&GetDigit(Mid(MyNumber,

3))61.End If 62.GetHundreds = Result 63.End Function 64.'********************************************* 65.' Converts a number

from

10

to

99

into

text.*

66.'*********************************************

67.Function

GetTens(TensText)68.Dim Result As String 69.Result = ”“ 'null out the temporary function value 70.If Val(Left(TensText, 1))= 1 Then ' If value between 10-19 71.Select Case Val(TensText)72.Case 10: Result = ”Ten“ 73.Case 11: Result = ”Eleven“ 74.Case 12: Result = ”Twelve“ 75.Case 13: Result = ”Thirteen“ 76.Case 14: Result = ”Fourteen“ 77.Case 15: Result = ”Fifteen“ 78.Case 16:

Result

=

”Sixteen“

79.Case

17:

Result

= ”Seventeen“ 80.Case 18: Result = ”Eighteen“ 81.Case 19: Result = ”Nineteen“ 82.Case Else 83.End Select 84.Else ' If value between 20-99 85.Select Case Val(Left(TensText, 1))86.Case 2: Result = ”Twenty “ 87.Case 3: Result = ”Thirty “ 88.Case 4: Result = ”Forty “ 89.Case 5: Result = ”Fifty “ 90.Case 6: Result = ”Sixty “ 91.Case 7: Result = ”Seventy “ 92.Case 8: Result = ”Eighty “ 93.Case 9: Result = ”Ninety “ 94.Case Else 95.End Select 96.Result = Result &GetDigit _ 97.(Right(TensText, 1))'Retrieve ones place 98.End If 99.GetTens = Result 100.End Function 101.'******************************************* 102.' Converts

a

number

from

1

to

9

into

text.*

103.'*******************************************

104.Function

GetDigit(Digit)105.Select Case Val(Digit)106.Case 1: GetDigit = ”One“ 107.Case 2: GetDigit = ”Two“ 108.Case 3: GetDigit = ”Three“ 109.Case 4: GetDigit = ”Four“ 110.Case 5: GetDigit

= ”Five“ 111.Case 6: GetDigit = ”Six“ 112.Case 7: GetDigit = ”Seven“ 113.Case 8: GetDigit = ”Eight“ 114.Case 9: GetDigit = ”Nine“ 115.Case Else: GetDigit = ”\" 116.End Select 117.End Function 复制代码

然后在A1单元格输入需要的数值，在其他单元格输入=SpellNumber(A1)即可

第三篇：将一个数字字符串转换为整型数值

将一个数字字符串转换为整型数值 C++编程:将一个数字字符串转换为整型数 #include #include void main() { int a; cout< cin>>a; if(47>a || a>57) cout< else { char n; n=int(a);

第五篇：十进制数转换为二进制数

十进制数转换为二进制数 授课者：李老师

授课对象：普通中专学生  教学目标

知识目标：掌握十进制数转换为二进制数的方法 能力目标：培养学生逻辑思维能力与自学能力  教学重、难点

十进制数转换为二进制数的方法  教学方法 讲授和演示相结合  学情分析

通过上节课的学习,学生已经掌握了十进制、二进制、权和基数的基本概念，并且学会了二进制数转换为十进制数的方法，即“按权相加法”。在这堂课上，我将和学生一起探究和学习如何将十进制数转换为二进制数。

 教学过程 一、引入新课

师：同学们，上节课我们学习了二进制数转换为十进制数的方法，大家还记不记得是什么方法呢？

生：按权相加法。

师：对，比如说（11.01）2=1*20+1*21+0*2-1+1*2-2，这就是按权相加，那十进制数转换为二进制数是怎样转换的呢？这就是我们本节

例：将十进制数35.375转换为二进制数。解：a 整数部分 35÷2=17 余1 17÷2=8 余1 8÷2=4 余0 4÷2=2 余0 2÷2=1 余0 1÷2=0 余1 b 小数部分

0.375*2=0.75 „„ 0 0.75*2=1.5 „„ 1 0.5*2=1.0 „„ 1 c 整数与小数合并，可得：（35.375）10=（100011.011）2 三、练习

1.（43.675）10=（？）2

2.（71.6876）10=（？）2 通过练习，查看学生的掌握情况，检查学生完成情况，并指出错误，加以说明，同时，让学生自己小结，讨论本节课学到些什么？

四、课堂小结

本节课我们主要讲了十进制数转换为二进制数的方法，整数部分：“除2取余，逆序排列”；小数部分：“乘2取整，顺序排列”，最后再把整数部分和小数部分合并。

五、作业

课本32页的第一、二题。 逆序 顺序