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 ExplicitFunction 数字转英文(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = Thousand Place(3) = Million Place(4) = Billion Place(5) = Trillion MyNumber = Trim(Str(MyNumber)DecimalPlace = InStr(MyNumber, .)If DecimalPlace 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ 00, 2)MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)End IfCount = 1Do While MyNumber Temp = GetHundreds(Right(MyNumber, 3)If Temp Then Dollars = Temp & Place(Count) & DollarsIf Len(MyNumber) 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = End IfCount = Count + 1LoopSelect Case DollarsCase Dollars = No DollarsCase OneDollars = One DollarCase ElseDollars = Dollars & DollarsEnd SelectSelect Case CentsCase Cents = and No CentsCase OneCents = and One CentCase ElseCents = and & Cents & CentsEnd Select数字转英文 = Dollars & CentsEnd FunctionFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right(000 & MyNumber, 3)If Mid(MyNumber, 1, 1) 0 ThenResult = GetDigit(Mid(MyNumber, 1, 1) & Hundred End IfIf Mid(MyNumber, 2, 1) 0 ThenResult = Result & GetTens(Mid(MyNumber, 2)ElseResult = Result & GetDigit(Mid(MyNumber, 3)End IfGetHundreds = ResultEnd FunctionFunction GetTens(TensText)Dim Result As StringResult = If Val(Left(TensText, 1) = 1 ThenSelect Case Val(TensText)Case 10: Result = TenCase 11: Result = ElevenCase 12: Result = TwelveCase 13: Result = ThirteenCase 14: Result = FourteenCase 15: Result = FifteenCase 16: Result = SixteenCase 17: Result = SeventeenCase 18: Result = EighteenCase 19: Result = NineteenCase ElseEnd SelectElseSelect 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 ElseEnd SelectResult = Result & GetDigit _(Right(TensText, 1)End IfGetTens = ResultEnd Function Function GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = OneCase 2: GetDigit = TwoCase 3: GetDigit = ThreeCase 4: GetDigit = FourCase 5: GetDigit = FiveCase 6: GetDigit = SixCase 7: GetDigit = SevenCase 8: GetDigit = EightCase 9: GetDigit = NineCase Else: GetDigit = End SelectEnd Function5/现在回到Excel表格中，单击“B1”单元格，在菜单栏选择“插入”函数。6/在打开的“插入函数”对话框的“或选择类别”中选择“用户定义”，然后选择函数“数字转