EXCEL 中如何将数字转换成英文
在Excel里自由切换中英文标点
在Excel里自由切换中英文标点
在使用Excel时，往往既需要输入汉字，又需要输入数字。
这时最令人头疼的就是小数点的输入了。
因为在中文状态下，按下小数点键输入的就是中文的句号。
要解决这个问题可以如下设置：首先在E xcel 2007中选中需要输入汉字的单元格，单击“数据”选项卡“数据工具”组中的“数据有效性”按钮，在弹出的“数据有效性”对话框里单击“输入法模式”选项卡，将“模式”修改为“打开”，单击“确定”返回。
接着选择需要输入字母或数字的单元格区域，重复上述步骤，所不同的只是在“模式”框中选择“关闭（英文模式）”。
这样一来，当插入点处于要输入汉字的单元格时，系统就会自动切换到中文输入状态，处于字母或数字单元格时就是英文输入状态。
EXCEL文本函数-数字小写换大写英文大小写互换
EXCEL文本函数-数字小写换大写/英文大小写互换在大小写中英文互换公式中我们先假设每个表都有表头，且数字在第二行，在A2单元格内注意：公式可以随意拖动至任意指定单元格，公式内对象不变；可复制（待鼠标变成黑色十字再拖)；也可利用查找-替换-全部替换单元格如A2---全部换成F15EXCEL中数字小写换大写方法一：在单元格A2中输入小写数字123.12B2处输入以下公式=SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负","")&TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF (ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(ISERR (FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND (A2,2)),"[DBNum2]")&"分",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","")EXCEL中数字小写换大写方法二：在单元格A2中输入小写数字123.12B2处输入以下公式=IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-IN T((A2)*10)*10)=0,"整","零"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"),TE XT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10) -INT(A2)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"))EXCEL中数字小写换大写方法三：=IF(A2-INT(A2)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆整",TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆"&TEXT(INT((A2-INT(A2))*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT((A2*10-INT(A2* 10))*10),"[DBNum2]G/通用格式")&"分")EXCEL中数字小写换大写方法四：=IF((A2-INT(A2))=0,TEXT(A2,"[DBNUM2]")&"元整",IF(INT(A2*10)-A2*10=0,TEXT(INT(A2),"[DBNUM 2]")&"元"&TEXT((INT(A2*10)-INT(A2)*10),"[DBNUM2]")&"角整",TEXT(INT(A2),"[DBNUM2]")&"元"& IF(INT(A2*10)-INT(A2)*10=0,"零",TEXT(INT(A2*10)-INT(A2)*10,"[DBNUM2]")&"角")&TEXT(RIGHT (A2,1),"[DBNUM2]")&"分"))看看结果是不是出现了你想要的?应该有你需要的大写金额了公式的由来:仟分位公式如下:=TEXT(IF(LEN(INT($A1))>=4,MID($A1,LEN(INT($A1))-3,1),""),"[DBNum2]")佰分位公式如下:=TEXT(IF(LEN(INT($A1))>=3,MID($A1,LEN(INT($A1))-2,1),""),"[DBNum2]")第一位小数的公式:=TEXT(MID($A1,FIND(".",$A1)+1,1),"[DBNum2]")第二位小数的公式:=TEXT(MID(TEXT($A1,"0.00"),FIND(".",$A1)+2,1),"[DBNum2]") 其他依此类推用Excel函数将货币数值由阿拉伯数字自动生成为中文大写格式如图是全部用Excel函数自编农业银行转帐支票套打工具，图中内容是需要打印到转帐支票上内容，其中A-D列为支票存根部分，我们利用Excel函数对要填写大部分内容建立公式后，只要输入支票存根部分的三项基本信息（A6-A8单元格）：收款单位、金额、用途，其他内容全部自动生成。
阿拉伯数字转换成英文大写
启动Microsoft Excel。
按Alt+F11 启动Visual Basic 编辑器。
在“插入”菜单上，单击“模块”。
在模块表中键入下面的代码。
Option Explicit'Main FunctionFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(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 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) & Dollars If Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3) ElseMyNumber = ""End IfCount = Count + 1LoopSelect Case DollarsCase ""Dollars = "No Dollars"Case "One"Dollars = "One Dollar"Case ElseDollars = Dollars & " Dollars"End SelectSelect Case CentsCase ""Cents = " and No Cents"Case "One"Cents = " and One Cent"Case ElseCents = " and " & Cents & " Cents"End SelectSpellNumber = Dollars & CentsEnd Function' Converts a number from 100-999 into textFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right("000" & MyNumber, 3)' Convert the hundreds place.If Mid(MyNumber, 1, 1) <> "0" ThenResult = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "End If' Convert the tens and ones place.If Mid(MyNumber, 2, 1) <> "0" ThenResult = Result & GetTens(Mid(MyNumber, 2)) ElseResult = Result & GetDigit(Mid(MyNumber, 3)) End IfGetHundreds = ResultEnd Function' Converts a number from 10 to 99 into text.Function GetTens(TensText)Dim Result As StringResult = "" ' 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 ElseEnd SelectElse ' 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 ElseEnd SelectResult = Result & GetDigit _(Right(TensText, 1)) ' Retrieve ones place.End IfGetTens = ResultEnd 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 SelectEnd Function如何使用SpellNumber 示例函数要使用该示例函数将某数字更改为书面文本，请使用下列示例中演示的方法之一：方法1：直接输入通过将下面的公式输入单元格中，可以将32.50 更改为“Thirty Two Dollars and Fifty Cents”：=SpellNumber(32.50)方法2：单元格引用可以引用工作簿中的其他单元格。
excel 表格自动翻译公式
Excel表格自动翻译公式是一项非常实用和高效的功能，它可以帮助用户在处理多语言数据时节省大量的时间和精力。
在Excel中，我们经常会遇到需要处理多语言的情况，比如国际贸易、跨境业务等。
而使用表格自动翻译公式，可以让我们轻松地将一个语言的数据自动翻译成另一种语言，大大提高了工作效率。
我们来看一下如何在Excel中使用表格自动翻译公式。
在Excel中，我们可以使用GOOGLETRANSLATE函数来实现这个功能。
GOOGLETRANSLATE函数是一种可以将文本从一种语言翻译成另一种语言的函数，它的语法非常简单，只需要指定要翻译的文本和目标语言即可。
我们可以使用以下公式来将单元格A1中的英文文本翻译成中文：=GOOGLETRANSLATE(A1,"en","zh-CN")在这个公式中，A1是要翻译的文本，"en"表示原始文本的语言是英文，"zh-CN"表示要翻译成的目标语言是中文。
当我们在单元格A1中输入英文文本后，Excel会自动将其翻译成中文并显示在相邻的单元格中，非常方便快捷。
接下来，让我们来探讨一下表格自动翻译公式的深度应用。
除了单纯的文本翻译外，我们还可以结合其他函数和技巧，实现更加复杂和全面的自动翻译处理。
我们可以结合IF函数和VLOOKUP函数，实现根据不同条件选择不同语言翻译的功能；我们还可以通过自定义词典和翻译记忆库，优化和提升翻译的准确性和效率；甚至可以利用宏和VBA编程，实现更高级和定制化的自动翻译需求。
这些深度应用不仅可以帮助我们更好地处理多语言数据，还可以拓展我们在Excel中的应用能力和创造性。
Excel表格自动翻译公式是一项非常实用和高效的功能，它可以帮助我们轻松地处理多语言数据，节省大量的时间和精力。
在实际工作中，我们可以根据具体的需求和场景，灵活运用这项功能，实现更加高效和精准的数据处理和管理。
EXCEL文本函数-数字小写换大写英文大小写互换
EXCEL文本函数-数字小写换大写/英文大小写互换2007年10月17日星期三 13:04在大小写中英文互换公式中我们先假设每个表都有表头，且数字在第二行，在A2单元格内注意：公式可以随意拖动至任意指定单元格，公式内对象不变；可复制（待鼠标变成黑色十字再拖)；也可利用查找-替换-全部替换单元格如A2---全部换成F15EXCEL中数字小写换大写方法一：在单元格A2中输入小写数字123.12B2处输入以下公式=SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负","")&TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(I SERR(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分",IF(ROUND(A2,2)=0,"","整")),"零元零",""),"零元","")EXCEL中数字小写换大写方法二：在单元格A2中输入小写数字123.12B2处输入以下公式=IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,"整","零"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"))EXCEL中数字小写换大写方法三：=IF(A2-INT(A2)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆整",TEXT(INT(A2),"[DBNum2]G/通用格式")&"圆"&TEXT(INT((A2-INT(A2))*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT((A2*10-INT(A2*10))*10),"[DBNum2]G/通用格式")&"分")EXCEL中数字小写换大写方法四：=IF((A2-INT(A2))=0,TEXT(A2,"[DBNUM2]")&"元整",IF(INT(A2*10)-A2*10=0,TEXT(INT(A2),"[DBNUM2]")&"元"&TEXT((INT(A2*10)-INT(A2)*10),"[DBNUM2]")&"角整",TEXT(INT(A2),"[DBNUM2]")&"元"&IF(INT(A2*10)-INT(A2)*10=0,"零",TEXT(INT(A2*10)-INT(A2)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A2,1),"[DBNUM2]")&"分"))看看结果是不是出现了你想要的?应该有你需要的大写金额了公式的由来:仟分位公式如下:=TEXT(IF(LEN(INT($A1))>=4,MID($A1,LEN(INT($A1))-3,1),""),"[DBNum2]")佰分位公式如下:=TEXT(IF(LEN(INT($A1))>=3,MID($A1,LEN(INT($A1))-2,1),""),"[DBNum2]")第一位小数的公式:=TEXT(MID($A1,FIND(".",$A1)+1,1),"[DBNum2]")第二位小数的公式:=TEXT(MID(TEXT($A1,"0.00"),FIND(".",$A1)+2,1),"[DBNum2]") 其他依此类推^^^^^^^^^^^^^^^^用Excel函数将货币数值由阿拉伯数字自动生成为中文大写格式如图是全部用Excel函数自编农业银行转帐支票套打工具，图中内容是需要打印到转帐支票上内容，其中A-D列为支票存根部分，我们利用Excel函数对要填写大部分内容建立公式后，只要输入支票存根部分的三项基本信息（A6-A8单元格）：收款单位、金额、用途，其他内容全部自动生成。
EXCEL中如何将数字转换成英文
EXCEL中如何将数字转换成英文一、网上下载的代码1.创建一个模块：在SHEET上，右键－》查看代码。
选中“模块”－》插入模块。
2.写代码：Option ExplicitDim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberT oString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As String Dim Point As IntegerDim nBit As IntegerDim CurString As StringDim nNumLen As IntegerDim T As StringCall InitStr = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)T = Right(Str, Len(Str) - InStr(1, Str, "."))If Len(T) < 2 Then AfterPoint = Val(T) * 10If Len(T) = 2 Then AfterPoint = Val(T)If Len(T) > 2 Then AfterPoint = Val(Left(T, 2))End IfIf Len(BeforePoint) > 12 ThenNumberT oString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then'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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then'tmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3)) If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrIf Len(AfterPoint) > 0 ThenAfterPoint =Unit(6) & " " & DecodeHundred(AfterPoint) & " " & Unit(5)ElseAfterPoint = Unit(5)End IfNumberT oString = BeforePoint & " " & AfterPointEnd FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1))) End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2)) ElseDecodeHundred = DecodeHundred(Right(HundredString, 2)) End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub保存此代码到本地3.模块中已经定义了函数名称：NumberToString 直接当作EXCEL本地函数使用，例如在A1=7,在B1中输入＝NumberToString （A1）就可以拉！很简单的吧？二、以川改过的代码：'功能模块：数字转英文(货币)大写'Public Function NumberT oString(Number As Double) As String'调用形式：debug.print NumberToString(1234.32)'说明：最大支持12位数字，小数点后精确两位'程序：杨鑫光（Volitation）Dim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberT oString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As StringDim Point As IntegerDim nBit As IntegerDim CurString As StringCall Init'//开始处理Str = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)AfterPoint = Right(Str, Len(Str) - InStr(1, Str, "."))End IfIf Len(BeforePoint) > 12 ThenNumberT oString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 ThentmpStr = 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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 ThentmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3)) If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrNumberT oString = BeforePoint & " "End FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1)))End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2)) ElseDecodeHundred = DecodeHundred(Right(HundredString, 2)) End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub三、是我改过的代码：Option ExplicitDim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberT oString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As StringDim Point As IntegerDim nBit As IntegerDim CurString As StringDim nNumLen As IntegerDim T As StringCall InitStr = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)T = Right(Str, Len(Str) - InStr(1, Str, "."))If Len(T) < 2 Then AfterPoint = Val(T) * 10If Len(T) = 2 Then AfterPoint = Val(T)If Len(T) > 2 Then AfterPoint = Val(Left(T, 2))End IfIf Len(BeforePoint) > 12 ThenNumberT oString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then'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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then'tmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3)) If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrIf Len(AfterPoint) > 0 ThenAfterPoint = Unit(6) & " " & DecodeHundred(AfterPoint)ElseAfterPoint = Unit(5)End IfNumberT oString = BeforePoint & " " & AfterPointEnd FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1)))End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2)) ElseDecodeHundred = DecodeHundred(Right(HundredString, 2)) End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub。
EXCEL数字转大写英文
'此过程为西人所写，谨推荐给大家.'****************' Main Function *'****************Function SayUSD(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringApplication.Volatile TruePlace(2) = " Thousand "Place(3) = " Million "Place(4) = " Billion "Place(5) = " Trillion " ' String representation of amountMyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if noneDecimalPlace = InStr(MyNumber, ".")'Convert cents and set MyNumber to dollar amountIf 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 Dollars"Case "One"Dollars = "One Dollar"Case ElseDollars = Dollars & " Dollars"End SelectSelect Case CentsCase ""Cents = ""Case "One"Cents = " and One Cent"Case ElseCents = " and " & Cents & " Cents"End SelectSayUSD = Dollars & CentsEnd Function'*******************************************' Converts a number from 100-999 into text *'*******************************************Function GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place If Mid(MyNumber, 1, 1) <> "0" ThenResult = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "End If'Convert the tens and ones placeIf Mid(MyNumber, 2, 1) <> "0" ThenResult = Result & GetTens(Mid(MyNumber, 2))ElseResult = Result & GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd Function'*********************************************' Converts a number from 10 to 99 into text. *'*********************************************Function GetTens(TensText)Dim Result As StringResult = "" 'null out the temporary function valueIf Val(Left(TensText, 1)) = 1 Then ' If value between 10-19Select 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 ElseEnd SelectElse ' 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 ElseEnd SelectResult = Result & GetDigit _(Right(TensText, 1)) 'Retrieve ones placeEnd IfGetTens = ResultEnd 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 SelectEnd Function。
excell中数字转化为字母的方法
excell中数字转化为字母的方法【实用版3篇】目录（篇1）1.引言2.Excel 中数字转换为字母的方法2.1 使用公式2.2 使用条件格式2.3 使用自定义函数3.结论正文（篇1）【引言】在 Excel 中，有时我们需要将数字转换为字母，以便更好地进行数据处理和分析。
本文将为大家介绍三种在 Excel 中实现数字转换为字母的方法。
【Excel 中数字转换为字母的方法】【2.1 使用公式】我们可以使用公式将数字转换为字母。
具体操作如下：1.假设我们需要将 A1 单元格的数字转换为字母，我们在 B1 单元格输入以下公式：=CHAR(CODE(A1,16)+64)。
2.按 Enter 键，B1 单元格将显示 A1 单元格数字对应的字母。
【2.2 使用条件格式】条件格式是 Excel 内置的一种功能，可以用于根据特定条件自动更改单元格的显示格式。
具体操作如下：1.假设我们需要将 A1 单元格的数字转换为字母，我们在 A1 单元格右键单击，选择“格式刷”。
2.将格式刷应用到 A1 单元格，然后选择“数字”选项卡。
3.在“数字”选项卡中，选择“自定义”选项，并在右侧输入框中输入以下公式：=TEXT(A1, "0000-00-00")&" "&TEXT(A1, "0000-00-00")&" "&TEXT(A1, "0000-00-00")。
4.点击“确定”，A1 单元格的数字将被转换为字母并显示。
【2.3 使用自定义函数】我们还可以使用自定义函数来实现数字转换为字母。
具体操作如下：1.在 Excel 中，选择“开发”选项卡，然后点击“Visual Basic”。
2.在 Visual Basic 编辑器中，点击“插入”菜单，然后选择“模块”。
3.在模块中输入以下代码：```Function ConvertToLetters(num As Integer) As StringDim str As StringDim i As IntegerFor i = 1 To 10If num Mod 10 = i Thenstr = str & Chr(64 + i)End IfNext iConvertToLetters = strEnd Function```4.关闭 Visual Basic 编辑器，然后在 A1 单元格输入以下公式：=ConvertToLetters(A1)。
excel中数字转英文描述描述公式
excel中数字转英文描述描述公式【实用版】目录1.引言：介绍 Excel 中数字转英文描述的功能及其应用场景2.方法一：使用 Excel 内置的"自定义数字格式"功能3.方法二：使用公式和函数实现数字转英文描述4.方法三：使用第三方插件或软件5.结语：总结三种方法的优缺点及适用情况正文Excel 作为一款功能强大的表格处理软件，广泛应用于各种办公场景。
在日常使用中，我们可能需要将数字转换为英文描述，以满足特定的展示或分析需求。
下面我们将介绍三种在 Excel 中实现数字转英文描述的方法。
方法一：使用 Excel 内置的"自定义数字格式"功能。
1.选中需要转换的数字单元格或区域；2.右键点击，选择“格式单元格”；3.在“数字”选项卡中，选择“自定义”；4.在右侧的“类型”栏中，输入以下代码并按回车：```#.0000```其中，前面的“#”表示数字的正负符号将作为前缀，后面的四个零表示保留四位小数；5.点击“确定”按钮，数字将转换为带有英文前缀和后缀的格式。
方法二：使用公式和函数实现数字转英文描述。
1.在一个空白单元格中输入以下公式：```=IF(A1>0, "Positive", "Negative")&A1&" Zero"```其中，A1 为需要转换的数字单元格；2.将公式复制到需要应用的数字单元格或区域；3.根据需要调整公式中的"Positive"、"Negative"和"Zero"等文本。
方法三：使用第三方插件或软件。
1.在网上搜索并下载适用于 Excel 的数字转英文描述插件或软件；2.按照软件的说明安装并运行；3.使用软件的功能将数字转换为英文描述。
综上所述，Excel 中数字转英文描述的方法有多种，用户可以根据实际需求和操作习惯选择合适的方法。
excel中数字转英文描述描述公式
Excel表格中，有时需要将数字转换为相应的英文描述，以便更直观地呈现数据。
这在报表制作、数据分析等工作中非常常见，因此有必要掌握其中的转换方法。
本文将围绕这一主题，介绍在Excel中将数字转换为英文描述的公式和技巧。
一、基本概念在进行数字转换为英文描述之前，首先需要了解基本概念。
在Excel 中，通常使用IF函数或VLOOKUP函数来实现这一转换。
IF函数可以根据特定的条件返回不同的值，而VLOOKUP函数则可以在数据表中查找指定数值并返回相应的描述。
二、使用IF函数进行转换1. IF函数基本语法IF函数的基本语法为：=IF(条件, 返回值1, 返回值2)，其中条件为判断依据，返回值1为条件成立时的返回结果，返回值2为条件不成立时的返回结果。
可以根据实际情况嵌套多个IF函数，实现多条件下的转换。
2. 示例假设有一个销售报表，其中数字表示销售额。
我们希望将这些数字转换为销售等级，如“高”，“中”，“低”等。
可以使用IF函数完成这一转换：=IF(A2>1000, "高", IF(A2>500, "中", "低"))上述公式中，A2为销售额所在单元格，根据销售额不同的大小，返回相应的销售等级。
这样，便可以更直观地了解不同销售额对应的等级。
三、使用VLOOKUP函数进行转换1. VLOOKUP函数基本语法VLOOKUP函数的基本语法为：=VLOOKUP(查找值, 表格区域, 返回列号, FALSE)，其中查找值为要查找的数值，表格区域为要进行查找的数据表范围，返回列号为要返回的描述所在列号，FALSE表示精确匹配。
2. 示例假设有一个成绩表，其中数字表示分数。
我们希望将这些数字转换为成绩等级，如“A”，“B”，“C”等。
可以使用VLOOKUP函数完成这一转换：=VLOOKUP(B2, F2:G5, 2, FALSE)上述公式中，B2为分数所在单元格，F2:G5为成绩对照表的范围，2表示返回的是成绩等级所在的第二列。
excell中数字转化为字母的方法
excell中数字转化为字母的方法（原创实用版3篇）目录（篇1）1.引言：介绍 Excel 中数字转化为字母的方法的重要性2.方法一：使用"CHAR"函数3.方法二：使用"CODE"和"CHAR"函数4.方法三：使用"&"符号连接数字和字母5.总结：对比三种方法的优缺点，给出使用建议6.结语：鼓励读者尝试并掌握这些方法正文（篇1）在 Excel 中，有时我们需要将数字转换为字母，例如将序列号转换为字母表示。
这里我们将介绍三种常用的方法来实现这一目的。
方法一：使用"CHAR"函数。
在 Excel 中，"CHAR"函数可以将数字转换为对应的字母。
具体操作如下：1.在一个单元格中输入数字，例如 1；2.在另一个单元格中输入公式`=CHAR(A1)`，其中 A1 为包含数字的单元格；3.按 Enter 键，即可得到字母 A。
方法二：使用"CODE"和"CHAR"函数。
这种方法可以实现更复杂的转换，例如将数字转换为大写字母。
操作步骤如下：1.在一个单元格中输入数字，例如 1；2.在另一个单元格中输入公式`=CHAR(CODE(A1,163))`,其中 A1 为包含数字的单元格，163 表示大写字母 A 的 Unicode 编码；3.按 Enter 键，即可得到字母 A。
方法三：使用"&"符号连接数字和字母。
这种方法适用于将数字的一部分转换为字母。
例如，如果你想将序列号 123 中的最后两位转换为字母，可以这样做：1.在一个单元格中输入数字 123；2.在另一个单元格中输入公式`=A1&CHR(108)&CHR(109)`，其中 A1 为包含数字的单元格，108 和 109 分别表示字母 h 和 i 的 Unicode 编码；3.按 Enter 键，即可得到 1h3。
excel中数字转英文描述描述公式
excel中数字转英文描述描述公式Excel中数字转英文描述公式在Excel中，数字转英文描述是一种将数值转换为对应的英文描述的操作。
这个功能在某些应用场景下非常有用，比如财务报表、数据报表等需要将数值更加直观地呈现给读者的情况。
Excel中数字转英文描述的公式可以使用VBA宏编程实现，或者利用Excel内置函数完成。
下面我将介绍两种方法供大家参考。
方法一：VBA宏编程使用VBA宏编程可以自定义函数，实现数字转英文描述的功能。
下面是一个示例：```Function NumberToWords(ByVal MyNumber)Dim Units As StringDim DecimalPlace As StringDim Count As StringDim Temp As StringDim DecimalSeparator As StringDim CountText As StringDim PartA As StringDim PartB As StringDim Index As IntegerDecimalSeparator = "."ReDim Place(9) As StringPlace(2) = " Thousand "Place(3) = " Million "Place(4) = " Billion "Place(5) = " Trillion "MyNumber = Trim(CStr(MyNumber))Count = 1If MyNumber <> "" ThenDecimalPlace = InStr(MyNumber, DecimalSeparator)MyNumber = Left(MyNumber, DecimalPlace - 1)Do While MyNumber <> ""CountText = GetHundreds(Right(MyNumber, 3))If CountText <> "" Then Temp = CountText & Place(Count) & Temp If Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ""End IfCount = Count + 1LoopNumberToWords = TempEnd IfEnd FunctionPrivate Function GetDigit(ByVal Digit As String) As String 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"End SelectEnd FunctionPrivate Function GetTens(TensText As String) As StringDim Unit As StringUnit = Trim(Left(TensText, 1))Select Case Val(TensText)Case 10: GetTens = "Ten"Case 11: GetTens = "Eleven"Case 12: GetTens = "Twelve"Case 13: GetTens = "Thirteen"Case 14: GetTens = "Fourteen"Case 15: GetTens = "Fifteen"Case 16: GetTens = "Sixteen"Case 17: GetTens = "Seventeen"Case 18: GetTens = "Eighteen"Case 19: GetTens = "Nineteen"Case 20: GetTens = "Twenty"Case 30: GetTens = "Thirty"Case 40: GetTens = "Forty"Case 50: GetTens = "Fifty"Case 60: GetTens = "Sixty"Case 70: GetTens = "Seventy"Case 80: GetTens = "Eighty"Case 90: GetTens = "Ninety"Case Else: GetTens = GetDigit(Unit) & " " & GetDigit(Trim(Right(TensText, 1)))End SelectEnd FunctionPrivate Function 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 Function```要使用以上VBA宏编程实现的数字转英文描述的函数，您需要按照以下步骤进行操作：1. 打开Excel文件，按下`ALT+F11`打开VBA编辑器；2. 在左侧的“项目资源管理器”窗口中，找到您的工作簿；3. 右键单击并选择“插入”->“模块”；4. 将上述代码复制粘贴到模块中；5. 返回Excel文件，您可以在任何单元格中使用该函数，例如，输入`=NumberToWords(1234)`，即可将数字1234转换为英文描述。
excel中数字转英文描述描述公式
excel中数字转英文描述描述公式摘要：1.引言：介绍Excel 中数字转英文描述的功能及其应用场景2.方法一：使用条件格式实现数字转英文描述2.1 设置新规则2.2 应用规则2.3 调整格式3.方法二：使用自定义函数实现数字转英文描述3.1 创建自定义函数3.2 调用自定义函数4.方法三：使用第三方插件实现数字转英文描述4.1 安装插件4.2 使用插件功能5.结论：总结三种方法的优缺点及适用情况正文：在Excel 中，将数字转换为英文描述的功能可以帮助用户更直观地阅读和理解数据。
例如，在处理财务报表时，将数字金额转换为“$1,000”这样的英文描述可以提高数据的可读性。
下面我们将介绍三种实现数字转英文描述的方法。
方法一：使用条件格式实现数字转英文描述。
步骤如下：1.设置新规则：选中需要转换的数字单元格或区域，点击“条件格式”>“新建规则”>“使用公式确定要设置格式的单元格”。
2.应用规则：在“格式值等于以下公式时”输入框中，输入以下公式：`=OR(ABS(A1)>=1000,ABS(A1)>=10000,ABS(A1)>=100000))`，其中A1 为需要转换的单元格。
然后点击“设置格式”。
3.调整格式：在弹出的“设置格式”窗口中，选择“数字”选项卡，将数字格式设置为“0,000.00”，并将小数点后的数字设置为0。
接着点击“确定”，最后点击“确定应用”。
方法二：使用自定义函数实现数字转英文描述。
步骤如下：1.创建自定义函数：在Excel 中按下“Alt+F11”打开VBA 编辑器，点击“插入”>“模块”，在新模块中输入以下代码：`FunctionEngName(num As Double) As StringDim str As StringIf num >= 1000 Then str = "千" & EngName(num / 1000)ElseIf num >= 100 Then str = "百" & EngName(num / 100)ElseIf num >= 10 Then str = "十" & EngName(num / 10)Else str = numEnd IfEngName = strEnd Function`2.调用自定义函数：在需要转换的单元格中输入以下公式：`=ENGNAME(A1)`，其中A1 为需要转换的单元格。
excel中数字转英文描述描述公式
excel中数字转英文描述描述公式（最新版）目录1.引言：介绍 Excel 中数字转英文描述的功能及应用场景2.方法一：使用 Excel 内置的"数字转英文"功能3.方法二：使用自定义函数实现数字转英文描述4.方法三：使用第三方插件进行数字转英文描述5.总结：对比三种方法的优缺点，给出使用建议正文在 Excel 中，将数字转换为英文描述的功能可以帮助用户更直观地理解和展示数据。
下面将介绍三种实现数字转英文描述的方法，并进行优缺点对比，以帮助您选择合适的方法。
方法一：使用 Excel 内置的"数字转英文"功能1.1 选中需要转换的数字单元格或区域1.2 在“开始”选项卡中找到“数字”分类1.3 点击“数字格式”下拉按钮，选择“自定义”1.4 在“类型”栏中输入“[0-9]”，在“格式”栏中输入需要的英文描述，例如“#.###”表示“零点###”1.5 点击“确定”，完成数字转英文描述优点：操作简单，无需编写代码，适用于较小范围的数据转换。
缺点：只能实现单一的数字格式转换，无法满足复杂的描述需求。
方法二：使用自定义函数实现数字转英文描述2.1 创建一个新的 Excel 工作表，并在其中输入以下公式：```=IF(A2<1, "零", IF(A2<10, TEXT(A2, "0")&"十", TEXT(A2, "00")&"百")))&"点"&IF(MOD(A2, 1)=0, "", TEXT(MOD(A2, 1), "0")) ```2.2 将公式向下填充，以适应不同的数字范围2.3 复制公式所在的列，将其粘贴到需要转换的数字列优点：可以自定义数字格式和描述，适用于复杂的需求。
excel中中文转换英文
excel中中文转换英文====================================== ====================== Excel中文转拼音（简称版）====================================== ======================打开Excel->工具->宏->Viaual Basic编辑器在弹出来的窗口中对着VBAproject点右键->插入->模块下面会出现一个名为"模块1"，粘贴以下内容：Function getpychar(char)tmp = 65536 + Asc(char)If (tmp >= 45217 And tmp <= 45252) Thengetpychar = "A"ElseIf (tmp >= 45253 And tmp <= 45760) Thengetpychar = "B"ElseIf (tmp >= 45761 And tmp <= 46317) Thengetpychar = "C"ElseIf (tmp >= 46318 And tmp <= 46825) Thengetpychar = "D"ElseIf (tmp >= 46826 And tmp <= 47009) Thengetpychar = "E"ElseIf (tmp >= 47010 And tmp <= 47296) Thengetpychar = "F"ElseIf (tmp >= 47297 And tmp <= 47613) Thengetpychar = "G"ElseIf (tmp >= 47614 And tmp <= 48118) Thengetpychar = "H"ElseIf (tmp >= 48119 And tmp <= 49061) Thengetpychar = "J"ElseIf (tmp >= 49062 And tmp <= 49323) Thengetpychar = "K"ElseIf (tmp >= 49324 And tmp <= 49895) Then getpychar = "L"ElseIf (tmp >= 49896 And tmp <= 50370) Then getpychar = "M"ElseIf (tmp >= 50371 And tmp <= 50613) Then getpychar = "N"ElseIf (tmp >= 50614 And tmp <= 50621) Then getpychar = "O"ElseIf (tmp >= 50622 And tmp <= 50905) Then getpychar = "P"ElseIf (tmp >= 50906 And tmp <= 51386) Then getpychar = "Q"ElseIf (tmp >= 51387 And tmp <= 51445) Then getpychar = "R"ElseIf (tmp >= 51446 And tmp <= 52217) Then getpychar = "S"ElseIf (tmp >= 52218 And tmp <= 52697) Then getpychar = "T"ElseIf (tmp >= 52698 And tmp <= 52979) Then getpychar = "W"ElseIf (tmp >= 52980 And tmp <= 53640) Then getpychar = "X"ElseIf (tmp >= 53689 And tmp <= 54480) Then getpychar = "Y"ElseIf (tmp >= 54481 And tmp <= 62289) Then getpychar = "Z"Else '如果不是中文，则不处理getpychar = charEnd IfEnd FunctionFunction getpy(str)For i = 1 To Len(str)getpy = getpy & getpychar(Mid(str, i, 1))Next iEnd Function现在转换函数已编写完成!关掉此编缉的窗口。
excel数字转换字母函数
excel数字转换字母函数
在Excel中，数字和字母之间的转换是非常常见的操作。
例如，在编写某些公式时，需要将数字转换为相应的字母，或将字母转换为相应的数字。
这时，我们可以使用Excel中提供的“数字转换字母函数”来实现这个功能。
Excel中的数字转换字母函数包括两个函数：CHAR和CODE。
其中，CHAR函数可以将数字转换为相应的Unicode字符，而CODE函数则可以将字符转换为相应的Unicode编码。
例如，如果我们想将数字1转换为字母A，可以使用CHAR函数，如下所示：
=CHAR(64+1)
这里的64代表的是大写字母A的Unicode编码，因此，只需要将64和1相加即可得到大写字母A的Unicode编码。
同样地，如果我们想将字母A转换为数字1，可以使用CODE函数，如下所示：
=CODE('A')-64
这里的CODE函数可以将字母A转换为相应的Unicode编码，然后将64减去这个编码即可得到数字1。
需要注意的是，Excel中的数字转换字母函数只能转换大写字母。
如果需要转换小写字母，可以将转换之后的字符再使用UPPER函数进行转换。
- 1 -。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一、网上下载的代码1.创建一个模块：在SHEET上，右键－》查看代码。
选中“模块”－》插入模块。
2.写代码：Option ExplicitDim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberToString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As String Dim Point As IntegerDim nBit As IntegerDim CurString As StringDim nNumLen As IntegerDim T As StringCall InitStr = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)T = Right(Str, Len(Str) - InStr(1, Str, "."))If Len(T) < 2 Then AfterPoint = Val(T) * 10If Len(T) = 2 Then AfterPoint = Val(T)If Len(T) > 2 Then AfterPoint = Val(Left(T, 2))End IfIf Len(BeforePoint) > 12 ThenNumberToString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then'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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then'tmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3))If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrIf Len(AfterPoint) > 0 ThenAfterPoint =Unit(6) & " " & DecodeHundred(AfterPoint) & " " & Unit(5)ElseAfterPoint = Unit(5)End IfNumberToString = BeforePoint & " " & AfterPointEnd FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1))) End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2))ElseDecodeHundred = DecodeHundred(Right(HundredString, 2))End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub保存此代码到本地3.模块中已经定义了函数名称：NumberToString 直接当作EXCEL本地函数使用，例如在A1=7,在B1中输入＝NumberToString（A1）就可以拉！很简单的吧？二、以川改过的代码：'功能模块：数字转英文(货币)大写'Public Function NumberToString(Number As Double) As String'调用形式：debug.print NumberToString(1234.32)'说明：最大支持12位数字，小数点后精确两位'程序：杨鑫光（Volitation）Dim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberToString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As StringDim Point As IntegerDim nBit As IntegerDim CurString As StringCall Init'//开始处理Str = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)AfterPoint = Right(Str, Len(Str) - InStr(1, Str, "."))End IfIf Len(BeforePoint) > 12 ThenNumberToString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 ThentmpStr = 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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 ThentmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3))If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrNumberToString = BeforePoint & " "End FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1)))End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2))ElseDecodeHundred = DecodeHundred(Right(HundredString, 2))End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub三、是我改过的代码：Option ExplicitDim StrNO(19) As StringDim Unit(8) As StringDim StrTens(9) As StringPublic Function NumberToString(Number As Double) As StringDim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As StringDim Point As IntegerDim nBit As IntegerDim CurString As StringDim nNumLen As IntegerDim T As StringCall InitStr = CStr(Round(Number, 2))' Str = NumberIf InStr(1, Str, ".") = 0 ThenBeforePoint = StrAfterPoint = ""ElseBeforePoint = Left(Str, InStr(1, Str, ".") - 1)T = Right(Str, Len(Str) - InStr(1, Str, "."))If Len(T) < 2 Then AfterPoint = Val(T) * 10If Len(T) = 2 Then AfterPoint = Val(T)If Len(T) > 2 Then AfterPoint = Val(Left(T, 2))End IfIf Len(BeforePoint) > 12 ThenNumberToString = "Too Big."Exit FunctionEnd IfStr = ""Do While Len(BeforePoint) > 0nNumLen = Len(BeforePoint)If nNumLen Mod 3 = 0 ThenCurString = Left(BeforePoint, 3)BeforePoint = Right(BeforePoint, nNumLen - 3)ElseCurString = Left(BeforePoint, (nNumLen Mod 3))BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))End IfnBit = Len(BeforePoint) / 3tmpStr = DecodeHundred(CurString)If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 ThenIf CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then'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 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then'tmpStr = Unit(8) & " " & tmpStrEnd IfEnd IfIf nBit = 0 ThenStr = Trim(Str & " " & tmpStr)ElseStr = Trim(Str & " " & tmpStr & " " & Unit(nBit))End IfIf Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3))If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do'Debug.Print StrLoopBeforePoint = StrIf Len(AfterPoint) > 0 ThenAfterPoint = Unit(6) & " " & DecodeHundred(AfterPoint)ElseAfterPoint = Unit(5)End IfNumberToString = BeforePoint & " " & AfterPointEnd FunctionPrivate Function DecodeHundred(HundredString As String) As StringDim tmp As IntegerIf Len(HundredString) > 0 And Len(HundredString) <= 3 ThenSelect Case Len(HundredString)Case 1tmp = CInt(HundredString)If tmp <> 0 Then DecodeHundred = StrNO(tmp)Case 2tmp = CInt(HundredString)If tmp <> 0 ThenIf (tmp < 20) ThenDecodeHundred = StrNO(tmp)ElseIf CInt(Right(HundredString, 1)) = 0 ThenDecodeHundred = StrTens(Int(tmp / 10))ElseDecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1)))End IfEnd IfEnd IfCase 3If CInt(Left(HundredString, 1)) <> 0 ThenDecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2))ElseDecodeHundred = DecodeHundred(Right(HundredString, 2))End IfCase ElseEnd SelectEnd IfEnd FunctionPrivate Sub Init()If StrNO(1) <> "One" ThenStrNO(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"End IfEnd Sub。