EXCEL 中如何将数字转换成英文.docx
《EXCEL 中如何将数字转换成英文.docx》由会员分享,可在线阅读,更多相关《EXCEL 中如何将数字转换成英文.docx(17页珍藏版)》请在冰点文库上搜索。
EXCEL中如何将数字转换成英文
一、网上下载的代码
1.创建一个模块:
在SHEET上,右键-》查看代码。
选中“模块”-》插入模块。
2.写代码:
OptionExplicit
DimStrNO(19)AsString
DimUnit(8)AsString
DimStrTens(9)AsString
PublicFunctionNumberToString(NumberAsDouble)AsString
DimStrAsString,BeforePointAsString,AfterPointAsString,tmpStrAsString
DimPointAsInteger
DimnBitAsInteger
DimCurStringAsString
DimnNumLenAsInteger
DimTAsString
CallInit
Str=CStr(Round(Number,2))
'Str=Number
IfInStr(1,Str,".")=0Then
BeforePoint=Str
AfterPoint=""
Else
BeforePoint=Left(Str,InStr(1,Str,".")-1)
T=Right(Str,Len(Str)-InStr(1,Str,"."))
IfLen(T)<2ThenAfterPoint=Val(T)*10
IfLen(T)=2ThenAfterPoint=Val(T)
IfLen(T)>2ThenAfterPoint=Val(Left(T,2))
EndIf
IfLen(BeforePoint)>12Then
NumberToString="TooBig."
ExitFunction
EndIf
Str=""
DoWhileLen(BeforePoint)>0
nNumLen=Len(BeforePoint)
IfnNumLenMod3=0Then
CurString=Left(BeforePoint,3)
BeforePoint=Right(BeforePoint,nNumLen-3)
Else
CurString=Left(BeforePoint,(nNumLenMod3))
BeforePoint=Right(BeforePoint,nNumLen-(nNumLenMod3))
EndIf
nBit=Len(BeforePoint)/3
tmpStr=DecodeHundred(CurString)
If(BeforePoint=String(Len(BeforePoint),"0")OrnBit=0)AndLen(CurString)=3Then
IfCInt(Left(CurString,1))<>0AndCInt(Right(CurString,2))<>0Then
'tmpStr=Left(tmpStr,InStr(1,tmpStr,Unit(4))+Len(Unit(4)))&Unit(8)&""&Right(tmpStr,Len(tmpStr)-(InStr(1,tmpStr,Unit(4))+Len(Unit(4))))
Else'IfCInt(Left(CurString,1))<>0AndCInt(Right(CurString,2))=0Then
'tmpStr=Unit(8)&""&tmpStr
EndIf
EndIf
IfnBit=0Then
Str=Trim(Str&""&tmpStr)
Else
Str=Trim(Str&""&tmpStr&""&Unit(nBit))
EndIf
IfLeft(Str,3)=Unit(8)ThenStr=Trim(Right(Str,Len(Str)-3))
IfBeforePoint=String(Len(BeforePoint),"0")ThenExitDo
'Debug.PrintStr
Loop
BeforePoint=Str
IfLen(AfterPoint)>0Then
AfterPoint=Unit(6)&""&DecodeHundred(AfterPoint)&""&Unit(5)
Else
AfterPoint=Unit(5)
EndIf
NumberToString=BeforePoint&""&AfterPoint
EndFunction
PrivateFunctionDecodeHundred(HundredStringAsString)AsString
DimtmpAsInteger
IfLen(HundredString)>0AndLen(HundredString)<=3Then
SelectCaseLen(HundredString)
Case1
tmp=CInt(HundredString)
Iftmp<>0ThenDecodeHundred=StrNO(tmp)
Case2
tmp=CInt(HundredString)
Iftmp<>0Then
If(tmp<20)Then
DecodeHundred=StrNO(tmp)
Else
IfCInt(Right(HundredString,1))=0Then
DecodeHundred=StrTens(Int(tmp/10))
Else
DecodeHundred=StrTens(Int(tmp/10))&"-"&StrNO(CInt(Right(HundredString,1)))
EndIf
EndIf
EndIf
Case3
IfCInt(Left(HundredString,1))<>0Then
DecodeHundred=StrNO(CInt(Left(HundredString,1)))&""&Unit(4)&""&DecodeHundred(Right(HundredString,2))
Else
DecodeHundred=DecodeHundred(Right(HundredString,2))
EndIf
CaseElse
EndSelect
EndIf
EndFunction
PrivateSubInit()
IfStrNO
(1)<>"One"Then
StrNO
(1)="One"
StrNO
(2)="Two"
StrNO(3)="Three"
StrNO(4)="Four"
StrNO(5)="Five"
StrNO(6)="Six"
StrNO(7)="Seven"
StrNO(8)="Eight"
StrNO(9)="Nine"
StrNO(10)="Ten"
StrNO(11)="Eleven"
StrNO(12)="Twelve"
StrNO(13)="Thirteen"
StrNO(14)="Fourteen"
StrNO(15)="Fifteen"
StrNO(16)="Sixteen"
StrNO(17)="Seventeen"
StrNO(18)="Eighteen"
StrNO(19)="Nineteen"
StrTens
(1)="Ten"
StrTens
(2)="Twenty"
StrTens(3)="Thirty"
StrTens(4)="Forty"
StrTens(5)="Fifty"
StrTens(6)="Sixty"
StrTens(7)="Seventy"
StrTens(8)="Eighty"
StrTens(9)="Ninety"
Unit
(1)="Thousand"'材熌
Unit
(2)="Million"'材熌
Unit(3)="Billion"'材熌
Unit(4)="Hundred"
Unit(5)="Only"
Unit(6)="Point"
Unit(7)="Cents"
Unit(8)="And"
EndIf
EndSub
保存此代码到本地
3.模块中已经定义了函数名称:
NumberToString
直接当作EXCEL本地函数使用,例如在A1=7,在B1中输入=NumberToString(A1)就可以拉!
很简单的吧?
二、以川改过的代码:
'功能模块:
数字转英文(货币)大写
'PublicFunctionNumberToString(NumberAsDouble)AsString
'调用形式:
debug.printNumberToString(1234.32)
'说明:
最大支持12位数字,小数点后精确两位
'程序:
杨鑫光(Volitation)
DimStrNO(19)AsString
DimUnit(8)AsString
DimStrTens(9)AsString
PublicFunctionNumberToString(NumberAsDouble)AsString
DimStrAsString,BeforePointAsString,AfterPointAsString,tmpStrAsString
DimPointAsInteger
DimnBitAsInteger
DimCurStringAsString
CallInit
'//开始处理
Str=CStr(Round(Number,2))
'Str=Number
IfInStr(1,Str,".")=0Then
BeforePoint=Str
AfterPoint=""
Else
BeforePoint=Left(Str,InStr(1,Str,".")-1)
AfterPoint=Right(Str,Len(Str)-InStr(1,Str,"."))
EndIf
IfLen(BeforePoint)>12Then
NumberToString="TooBig."
ExitFunction
EndIf
Str=""
DoWhileLen(BeforePoint)>0
nNumLen=Len(BeforePoint)
IfnNumLenMod3=0Then
CurString=Left(BeforePoint,3)
BeforePoint=Right(BeforePoint,nNumLen-3)
Else
CurString=Left(BeforePoint,(nNumLenMod3))
BeforePoint=Right(BeforePoint,nNumLen-(nNumLenMod3))
EndIf
nBit=Len(BeforePoint)/3
tmpStr=DecodeHundred(CurString)
If(BeforePoint=String(Len(BeforePoint),"0")OrnBit=0)AndLen(CurString)=3Then
IfCInt(Left(CurString,1))<>0AndCInt(Right(CurString,2))<>0Then
tmpStr=Left(tmpStr,InStr(1,tmpStr,Unit(4))+Len(Unit(4)))&Unit(8)&""&Right(tmpStr,Len(tmpStr)-(InStr(1,tmpStr,Unit(4))+Len(Unit(4))))
Else'IfCInt(Left(CurString,1))<>0AndCInt(Right(CurString,2))=0Then
tmpStr=Unit(8)&""&tmpStr
EndIf
EndIf
IfnBit=0Then
Str=Trim(Str&""&tmpStr)
Else
Str=Trim(Str&""&tmpStr&""&Unit(nBit))
EndIf
IfLeft(Str,3)=Unit(8)ThenStr=Trim(Right(Str,Len(Str)-3))
IfBeforePoint=String(Len(BeforePoint),"0")ThenExitDo
'Debug.PrintStr
Loop
BeforePoint=Str
NumberToString=BeforePoint&""
EndFunction
PrivateFunctionDecodeHundred(HundredStringAsString)AsString
DimtmpAsInteger
IfLen(HundredString)>0AndLen(HundredString)<=3Then
SelectCaseLen(HundredString)
Case1
tmp=CInt(HundredString)
Iftmp<>0ThenDecodeHundred=StrNO(tmp)
Case2
tmp=CInt(HundredString)
Iftmp<>0Then
If(tmp<20)Then
DecodeHundred=StrNO(tmp)
Else
IfCInt(Right(HundredString,1))=0Then
DecodeHundred=StrTens(Int(tmp/10))
Else
DecodeHundred=StrTens(Int(tmp/10))&"-"&StrNO(CInt(Right(HundredString,1)))
EndIf
EndIf
EndIf
Case3
IfCInt(Left(HundredString,1))<>0Then
DecodeHundred=StrNO(CInt(Left(HundredString,1)))&""&Unit(4)&""&DecodeHundred(Right(HundredString,2))
Else
DecodeHundred=DecodeHundred(Right(HundredString,2))
EndIf
CaseElse
EndSelect
EndIf
EndFunction
PrivateSubInit()
IfStrNO
(1)<>"One"Then
StrNO
(1)="One"
StrNO
(2)="Two"
StrNO(3)="Three"
StrNO(4)="Four"
StrNO(5)="Five"
StrNO(6)="Six"
StrNO(7)="Seven"
StrNO(8)="Eight"
StrNO(9)="Nine"
StrNO(10)="Ten"
StrNO(11)="Eleven"
StrNO(12)="Twelve"
StrNO(13)="Thirteen"
StrNO(14)="Fourteen"
StrNO(15)="Fifteen"
StrNO(16)="Sixteen"
StrNO(17)="Seventeen"
StrNO(18)="Eighteen"
StrNO(19)="Nineteen"
StrTens
(1)="Ten"
StrTens
(2)="Twenty"
StrTens(3)="Thirty"
StrTens(4)="Forty"
StrTens(5)="Fifty"
StrTens(6)="Sixty"
StrTens(7)="Seventy"
StrTens(8)="Eighty"
StrTens(9)="Ninety"
Unit
(1)="Thousand"'第一个三位
Unit
(2)="Million"'第二个三位
Unit(3)="Billion"'第三个三位
Unit(4)="Hundred"
Unit(8)="And"
EndIf
EndSub
三、是我改过的代码:
OptionExplicit
DimStrNO(19)AsString
DimUnit(8)AsString
DimStrTens(9)AsString
PublicFunctionNumberToString(NumberAsDouble)AsString
DimStrAsString,BeforePointAsString,AfterPointAsString,tmpStrAsString
DimPointAsInteger
DimnBitAsInteger
DimCurStringAsString
DimnNumLenAsInteger
DimTAsString
CallInit
Str=CStr(Round(Number,2))
'Str=Number
IfInStr(1,Str,".")=0Then
BeforePoint=Str
AfterPoint=""
Else
BeforePoint=Left(Str,InStr(1,Str,".")-1)
T=Right(Str,Len(Str)-InStr(1,Str,"."))
IfLen(T)<2ThenAfterPoint=Val(T)*10
IfLen(T)=2ThenAfterPoint=Val(T)
IfLen(T)>2ThenAfterPoint=Val(Left(T,2))
EndIf
IfLen(BeforePoint)>12Then
NumberToString="TooBig."
ExitFunction
EndIf
Str=""
DoWhileLen(BeforePoint)>0
nNumLen=Len(BeforePoint)
IfnNumLenMod3=0Then
CurString=Left(BeforePoint,3)
BeforePoint=Right(BeforePoint,nNumLen-3)
Else
CurString=Left(BeforePoint,(nNumLenMod3))
BeforePoint=Right(BeforePoint,nNumLen-(nNumLenMod3))
EndIf
nBit=Len(BeforePoint)/3
tmpStr=DecodeHundred(CurString)
If(BeforePoint=String(Len(BeforePoint),"0")OrnBit=0)