Excel中数字转英文大写

Excel拥有十分强大的功能,特别是综合运用Excel中提供的众多公式和函数,可以起到事半功倍的效果。比如在工作中,我们经常要把数字的货币金额转换成英文的大写形式。
这种转换比较麻烦,因为Excel中没有直接的函数可以转换,必须要综合运用多种函数和公式来实现。下面就介绍转换的方法:

操作方法

  • 01

    1、关于金额的大写 英文中金额的大写,由三个部分组成: “SAY + 货币”+ 大写数字(amount in words)+ ONLY(相当于我们的“整”)。 和汉语不同的是,数字中有零不用写出来, 而是把数字读法写出来即可。

  • 02

    2、分析数据结构 一般情况下,英文的数字是以“千分位”来分隔的,每三位数为一段,每一段的数字都有相同的规律: 个位数1-9一般以one到nine表示。 十位数为1时,和后面的个位数合在一起单独用一个单词表示。 十位数为2-9时,由表示十位数的twenty到ninety再加个位数的one到nine表示。 根据这样的规律,我们开始写公式。

  • 03

    3、先写小数位的公式 此例只考虑两位小数的情况。公式如下: =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"

  • 04

    4、再写“千位”以下的公式 公式如下: =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"}))

  • 05

    5、再写“千位”段的公式 公式如下: =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 ")

  • 06

    6、再写“百万”段的公式 公式如下: =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 ")

  • 07

    7、再写“十亿”段的公式 公式如下: =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 ")

  • 08

    8、最后组合公式如下: ="SAY 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"))

(0)

相关推荐

  • EXCEL中数字转人民币大写的两种方法

    我们在Excel中经常需要将数字转化为大写人民币,该怎么转化呢?下面小编给大家分享两种方法. 设置单元格格式 01 首先我们右键单击要设置的数字,然后选择设置单元格格式选项 02 在弹出的界面中选择特 ...

  • 怎样在EXCEL中数字转人民币大写

    对于财务和会计领域的朋友们来说,在excel在录入数据后,将数字自动转换为人民币大写是每天都要做的工作之一.由于使用的频繁和简便,IT部落窝小编今天专门写下这篇文章分享给大家. Excel中也提供了将 ...

  • Excel中数字小写转大写怎样实现

    处理财务相关表格时,经常需要同时显示小写数字和它对应的大写数字,怎样实现呢?网上很多朋友想到了个很复杂的公式.不过小编这里不推荐使用这种公式,原因很简单,公式太复杂了,记不住,一个不小心还很容易出错. ...

  • Excel中人民币小写转换大写技巧

    Excel中人民币小写转换大写技巧 步骤/方法 01 要将人民币小写金额转换成大写格式,用Excel提供的格式,将自定义格式类型中的"[dbnum2]G/通用格式"改为" ...

  • WPS表格中数字转换为人民币大写的方法

    WPS表格中数字转换为人民币大写的方法 1.打开WPS表格,选中要设置人民币大写的单元格,鼠标右键,点击"设置单元格格式",如下图. 2.弹出"单元格格式"对话 ...

  • 如何处理Excel中数字太长无法显示的问题?

    excle是数据处理软件,里面也有用文字标注的单元格,但是单元格长度就那么长,不可能每次为了一两个而拉的过长,那么如何处理Excel中数字太长无法显示的问题?今天就来教给大家吧~具体如下:1.首先,在 ...

  • excel中数字格式与文本格式之间的转换实例

    excel中数字格式与文本格式之间的转换实例. 操作方法 01 1 文本格式转为数字格式:下图是转换的数据源: 02 方法一:选择单元格区域,点鼠标右键,从菜单中选择"设置单元格格式&quo ...

  • Excel中数字无法居中的解决方法及原因分析

    Excel中数字无法居中,大家也工作也偶然遇到,有时候也摸不着头脑,到底是什么原因产生的呢? 操作方法 01 如图,单击居中按钮后,单元格里的数字根本就没有反应.到底什么原因,往下面看. 02 一直原 ...

  • Excel中数字如何自动转换成中文大写数字

    平时工作中输入金额时常常需要转成中文大写数字,但输入起来比较麻烦,那么是否可以将数字直接转换成中文大写呢?本教程中就为大家介绍在excel中如何将数字自动转换成中文大写数字。 第一步:打开Excel工 ...