⑴ 在EXCEL的一个表格中输入金额，在另一个表格中会自动翻译成大写
单击一个带转换的单元格，命名为X，然后在其他任何一单元格输入=IF(ROUND(x,2)<0,"无效数值",IF(ROUND(x,2)=0,"零",IF(ROUND(x,2)<1,"",TEXT(INT(ROUND(x,2)),"[dbnum2]")&"元")&IF(INT(ROUND(x,2)*10)-INT(ROUND(x,2))*10=0,IF(INT(ROUND(x,2))*(INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(x,2)*10)-INT(ROUND(x,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10)=0,"整",TEXT((INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10),"[dbnum2]")&"分")))，看看，保证符合财经大写的表达。
⑵ EXCEL函数如何将数字转化成英文大写金额
使用自定义函数
步骤：ALT+F11，插入模块，复制下方代码
然后在需要使用的地方输入公式：=SpellNumber(单元格地址)
效果图如下，B1公式为=SpellNumber(A1)
DimDollars,Temp
DimDecimalPlace,Count
ReDimPlace(9)AsString
Application.VolatileTrue
Place(2)="THOUSAND"
Place(3)="MILLION"
Place(4)="BILLION"
Place(5)="TRILLION"
MyNumber=Trim(Str(MyNumber))
DecimalPlace=InStr(MyNumber,".")
IfDecimalPlace>0Then
Cents=GetTens(Left(Mid(MyNumber,DecimalPlace+1)&"00",2))
MyNumber=Trim(Left(MyNumber,DecimalPlace-1))
EndIf
Count=1
DoWhileMyNumber<>""
Temp=GetHundreds(Right(MyNumber,3))
IfTemp<>""ThenDollars=Temp&Place(Count)&Dollars
IfLen(MyNumber)>3Then
MyNumber=Left(MyNumber,Len(MyNumber)-3)
Else
MyNumber=""
EndIf
Count=Count+1
Loop
SelectCaseDollars
Case""
Dollars=""
Case"One"
Dollars="OneDollar"
CaseElse
Dollars="USDOLLARS"&Dollars
EndSelect
SpellNumber=Dollars
EndFunction
FunctionGetHundreds(ByValMyNumber)
DimResultAsString
IfVal(MyNumber)=0ThenExitFunction
MyNumber=Right("000"&MyNumber,3)
IfMid(MyNumber,1,1)<>"0"Then
Result=GetDigit(Mid(MyNumber,1,1))&"HUNDRED"
EndIf
IfMid(MyNumber,2,1)<>"0"Then
Result=Result&GetTens(Mid(MyNumber,2))
Else
Result=Result&GetDigit(Mid(MyNumber,3))
EndIf
GetHundreds=Result
EndFunction
FunctionGetTens(TensText)
DimResultAsString
Result=""
IfVal(Left(TensText,1))=1Then
SelectCaseVal(TensText)
Case10:Result="Ten"
Case11:Result="ELEVEN"
Case12:Result="TWELVE"
Case13:Result="THIRTEEN"
Case14:Result="FOURTEEN"
Case15:Result="FIFTEEN"
Case16:Result="SIXTEEN"
Case17:Result="SEVENTEEN"
Case18:Result="EIGHTEEN"
Case19:Result="NINETEEN"
CaseElse
EndSelect
Else
SelectCaseVal(Left(TensText,1))
Case2:Result="ANDTWENTY"
Case3:Result="ANDTHIRTY"
Case4:Result="ANDFORTY"
Case5:Result="ANDFIFTY"
Case6:Result="ANDSIXTY"
Case7:Result="ANDSEVENTY"
Case8:Result="ANDEIGHTY"
Case9:Result="ANDNINETY"
CaseElse
EndSelect
Result=Result&GetDigit_
(Right(TensText,1))
EndIf
GetTens=Result
EndFunction
FunctionGetDigit(Digit)
SelectCaseVal(Digit)
Case1:GetDigit="ONE"
Case2:GetDigit="TWO"
Case3:GetDigit="THREE"
Case4:GetDigit="FOUR"
Case5:GetDigit="FIVE"
Case6:GetDigit="SIX"
Case7:GetDigit="SEVEN"
Case8:GetDigit="EIGHT"
Case9:GetDigit="NINE"
CaseElse:GetDigit=""
EndSelect
EndFunction
⑶ 在excel中将数字金额变成中文大写和英文金额
设置单元格格式，数字，特殊，中文大写数字，这样就可以了。要加一个元整，需要点了特殊之后，选中中文大写数字，然后点击自定义，会看见 [DBNum2][$-804]G/通用格式 这个时候在这句话后面加上 "元整" ，注意双引号要是英文输入状态下的双引号。
⑷ excel怎么把数字金额自动生成英文大写金额 比如100 写成One Hundred Only
需要用于VBA
网络经验帖：
http://jingyan..com/article/f3ad7d0ffe9a1409c2345b54.html
微软官网：
https://support.microsoft.com/zh-cn/kb/213360
⑸ excel中如何将阿拉伯数字自动转换成英文大写
具体公式如下：
B1公式下拉：
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1,2),TEXT(A1,";负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式圆;;")&TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"零分","整")
⑹ WPS表格文件里面的阿拉伯数字怎么能自动转换成英文金额呢
有两种方法可以把阿拉伯数字转换成英文。
一、用公式比较复杂
="US DOLLARS "&UPPER(TRIM(IF(VALUE(RIGHT(INT(A1/1000000000),3))=0,"",LOOKUP(VALUE(LEFT(TEXT(RIGHT(INT(A1/1000000000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;"","one","two","three","four","five","six","seven","eight","nine"})&IF(LEFT(TEXT(RIGHT(INT(A1/1000000000),3),"000"),1)="0"," "," hundred ")&IF(MID(TEXT(RIGHT(INT(A1/1000000000),3),"000"),2,1)="1",LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000000000),3),"000"),2,2)),{10,11,12,13,14,15,16,17,18,19;"ten","eleven","twelve","thirteen","fourteen","fiifteen","sixteen","seventeen","eighteen","nineteen"}),LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000000000),3),"000"),2,1)),{0,1,2,3,4,5,6,7,8,9;"","","twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"}))&IF(MID(TEXT(RIGHT(INT(A1/1000000000),3),"000"),2,1)="1","",LOOKUP(VALUE(RIGHT(TEXT(RIGHT(INT(A1/1000000000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"}))&" billion ")&IF(VALUE(RIGHT(INT(A1/1000000),3))=0,"",LOOKUP(VALUE(LEFT(TEXT(RIGHT(INT(A1/1000000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;"","one","two","three","four","five","six","seven","eight","nine"})&IF(LEFT(TEXT(RIGHT(INT(A1/1000000),3),"000"),1)="0"," "," hundred ")&IF(MID(TEXT(RIGHT(INT(A1/1000000),3),"000"),2,1)="1",LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000000),3),"000"),2,2)),{10,11,12,13,14,15,16,17,18,19;"ten","eleven","twelve","thirteen","fourteen","fiifteen","sixteen","seventeen","eighteen","nineteen"}),LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000000),3),"000"),2,1)),{0,1,2,3,4,5,6,7,8,9;"","","twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"}))&IF(MID(TEXT(RIGHT(INT(A1/1000000),3),"000"),2,1)="1","",LOOKUP(VALUE(RIGHT(TEXT(RIGHT(INT(A1/1000000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"}))&" million ")&IF(VALUE(RIGHT(INT(A1/1000),3))=0,"",LOOKUP(VALUE(LEFT(TEXT(RIGHT(INT(A1/1000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;"","one","two","three","four","five","six","seven","eight","nine"})&IF(LEFT(TEXT(RIGHT(INT(A1/1000),3),"000"),1)="0"," "," hundred ")&IF(MID(TEXT(RIGHT(INT(A1/1000),3),"000"),2,1)="1",LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000),3),"000"),2,2)),{10,11,12,13,14,15,16,17,18,19;"ten","eleven","twelve","thirteen","fourteen","fiifteen","sixteen","seventeen","eighteen","nineteen"}),LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1/1000),3),"000"),2,1)),{0,1,2,3,4,5,6,7,8,9;"","","twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"}))&IF(MID(TEXT(RIGHT(INT(A1/1000),3),"000"),2,1)="1","",LOOKUP(VALUE(RIGHT(TEXT(RIGHT(INT(A1/1000),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"}))&" thousand ")&LOOKUP(VALUE(LEFT(TEXT(RIGHT(INT(A1),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;"","one","two","three","four","five","six","seven","eight","nine"})&IF(LEFT(TEXT(RIGHT(INT(A1),3),"000"),1)="0"," "," hundred ")&IF(MID(TEXT(RIGHT(INT(A1),3),"000"),2,1)="1",LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1),3),"000"),2,2)),{10,11,12,13,14,15,16,17,18,19;"ten","eleven","twelve","thirteen","fourteen","fiifteen","sixteen","seventeen","eighteen","nineteen"}),LOOKUP(VALUE(MID(TEXT(RIGHT(INT(A1),3),"000"),2,1)),{0,1,2,3,4,5,6,7,8,9;"","","twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"}))&IF(MID(TEXT(RIGHT(INT(A1),3),"000"),2,1)="1","",LOOKUP(VALUE(RIGHT(TEXT(RIGHT(INT(A1),3),"000"),1)),{0,1,2,3,4,5,6,7,8,9;""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"}))&IF(VALUE(RIGHT(TEXT(A1,"0.00"),2))=0,""," AND CENTS "&IF(VALUE(RIGHT(TEXT(A1,"0.00"),2))<20,LOOKUP(VALUE(RIGHT(TEXT(A1,"0.00"),2)),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19;"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fiifteen","sixteen","seventeen","eighteen","nineteen"}),LOOKUP(VALUE(LEFT(RIGHT(TEXT(A1,"0.00"),2),1)),{2,3,4,5,6,7,8,9;"twenty","thirty","fourty","fifty","sixty","seventy","eighty","ninety"})&" "&LOOKUP(VALUE(RIGHT(TEXT(A1,"0.00"),1)),{0,1,2,3,4,5,6,7,8,9;"","one","two","three","four","five","six","seven","eight","nine"})))&" ONLY"))
⑺ excel怎么设置把金额自动转化为英文大写
按Alt-F11, 调出VBA，选插入->模块，然后在窗口中输入后面所附代码，按Alt-Q退出。
然后在需要英文数字的地方输入公式=SpellNumber(A1)，A1根据你数字的那个单元格进行修改。
就可以了。
'****************
' Main Function *
'****************
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
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 = "U.S. Dollars Zero"
Case "One"
Dollars = "U.S. Dollars One"
Case Else
Dollars = "U.S. Dollars " & Dollars
End Select
Select Case Cents
Case ""
Cents = " and Cents Zero"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and Cents " & Cents
End Select
SpellNumber = “Say ” & Dollars & Cents & "Only***"
SpellNumber = UCase(SpellNumber)
End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
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
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 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 ' If value between 20-99...
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)) ' Retrieve ones place.
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
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
⑻ excel2007版本中如何将阿拉伯数字自动转换成英文大写
选取数字区域,比如A1:A100,右键单击,之后,---设置单元格格式---数字---货币---在该页面右侧的货币符号框右部有一倒三角按钮,点击,选取美元符号,确定,即可.
⑼ excel中如何将阿拉伯数字自动转换成英文大写
自订函数的使用方法和内建函数大同小异。
使用插入函数的方式来使用该函数：
<一>
插入函数的方式可以从功能表的插入>>函数
或
一般工具列的自动加总旁按下拉式选单选其他函数
或
资料编辑列上的 Fx 按钮
以上三种方法皆可插入函数。
<二>
函数类别选使用者定义
<三>
函数名称选自订函数SpellNumber>>确定
<四>
输入数字或选参照的储存格>>确定
上述方式可能会依不同的 Excel 版本而略有差异。
自订函数的共用性与流通性
如果你要在本机的任何一个活页簿都可以使用这个函数，则建议你建立一个个人巨集活页簿，并将该函数放在个人巨集活页簿内。
如果要使用这个函数的活页簿会四处在网路上流通的话，则建议将该函数附属在该活页簿内，让它随着档案在网路上奔走，不然当档案到了其他人的电脑上，而其他人的 excel 内并无 SpellNumber 这个函数时，则会出现 #NAME? 的错误值。
已记录
--------------------------------------------------------------------------------
BEFORE ASKING, YOU SHOULD RTFH, RTFM, STFW...
leonchou
论坛维护群
离线
文章: 1160
Re: 将数字转换成英文的货币表示法
« 回覆文章 #1 于: 2005-03-27, 23:51:34 »
--------------------------------------------------------------------------------
上述的微软网页亦可由此进入
http://support.microsoft.com/support/kb/articles/Q213/3/60.ASP
另提供一以内建函数转英文大写金额的方式：
在此提供的是使用 Excel 内建工作表函数达成的
[数字转英文大写] 功能，无巨集、无VBA。
可自选货币（Currency）符号，适合外国支票、汇票或相关文件使用。
内建函数应该对一般人来说比较容易理解。
由于这个是以前用 Excel97 做的，如果你认为有任何
可改进之处，或新版有更理想的做法，
(其实新版应该要纳为内建功能了不是吗)
欢迎提出来讨论。
[2003/12/19]
1.修改为支援到千万(8位数)，
2.修正了小数超过两位时的Bug (采四舍五入方式)。
[2004/3/22]
修正没有小数时的 Bug
(例如 123 会显示成 ONE HUNDRED TWENTY THREE AND CENTS THREE)
[2004/4/9]
修正小数只有一位时的Bug，例如 123.40，它会显示成
one hundred twenty three and cents FORTY FOUR only.