问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

WPS Office (10.1.0.7106) excel如何吧阿拉伯数字转换成英文

发布网友 发布时间：2022-04-22 13:01

我来回答

共1个回答

热心网友 时间：2023-11-05 10:56

有两种方法可以把阿拉伯数字转换成英文。

一、用公式比较复杂

="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"))

二、你可以用VBA代码，ALT+F11——插入模块——粘贴如下代码：

Function SpellNumber(ByVal MyNumber)  

Dim Dollars, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Application.Volatile True

Place(2) = " THOUSAND "

Place(3) = " MILLION "

Place(4) = " BILLION "

Place(5) = " TRILLION "

MyNumber = Trim(Str(MyNumber))

DecimalPlace = InStr(MyNumber, ".")

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 = ""

Case "One"

Dollars = "One Dollar"

Case Else

Dollars = "US DOLLARS " & Dollars

End Select

SpellNumber = Dollars

End Function

Function GetHundreds(ByVal MyNumber)

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 = "AND TWENTY "

Case 3: Result = "AND THIRTY "

Case 4: Result = "AND FORTY "

Case 5: Result = "AND FIFTY "

Case 6: Result = "AND SIXTY "

Case 7: Result = "AND SEVENTY "

Case 8: Result = "AND EIGHTY "

Case 9: Result = "AND 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

最后，使用这个公式：=SpellNumber(A1)即可得出结果：

声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com
自离后能补办离职证明吗? 我是家里自离的,可以补办辞职手续吗 急救!!!狗狗受凉吃什么就吐什么,该吃什么药 狗狗受凉呕吐吃什么药好得快 公告栏标题里的字如何设定字体及大小 公告栏怎移动啊 我的公告栏 如何设置 怎么让QQ拍拍公告栏文字不滚动 在滚动的公告栏中鼠标放上去时就停止,这种效果怎么做?谢谢了 王者荣耀铂金1和黄金3双排排到的是什么段位的 四川重庆火锅配方？ 重庆火锅配方 重庆火锅的配方有哪些 wps表格列标数字怎么改变为A.B.C 重庆火锅底料的做法和配方 重庆正宗老火锅底料怎么制作 wps表格人名币大写如何转换？ 重庆火锅的配料有哪些 excel表格中数字转换成人名，如何转换。求高手指点 河南南阳独山玉与江苏邳州独山玉如何区别？ 独山玉和翡翠哪一个更好一点 独山玉和翡翠的区别，别把独山玉当成翡翠了 什么是独山玉？ 青海玉跟独山玉哪个好 独山玉和翡翠究竟有何差别 怎样鉴别独山玉的好坏 独山玉和翡翠的区别有哪些，独山玉什么样的最好 独龙玉与和田碧玉区别 翡翠与独山玉的区别是什么？ 独山玉与和田玉如何辨别 有谁知道重庆火锅做法讲解？ 支付宝帐户手机能注消吗？ 支付宝一个手机号可以注册几个 手机上可以注册支付宝吗 手机可以注册支付宝吗？ 手机上怎样注册开通支付宝 请问大家高位肠梗阻与低位肠梗阻呕吐的特点是什么啊？ 肠梗阻的临床表现痛、吐、胀、闭具体表现为什么？ 肠梗阻的临床表现有哪些？ 肠梗阻的症状有哪些？ 肠梗阻的四大症状? 肠梗阻是什么症状 404 Not Found 下列哪项不是高位肠梗阻呕吐的临床特点？ 肠梗阻有什么症状和表现 肠梗阻有那些症状，会出现剧烈腹痛吗真是痛到要命+ 肠梗阻一定会呕吐的吗？ 抖音上互相关注而关注上没有互相关注两个人还能聊天吗? 什么是肠梗阻，有什么症状 抖音关注别人跟对方留一句话马上取消别人别人知道吗？
懂视 51dongshi.com 版权所有
Copyright © 2019-2024