google-apps-script - 将 Google 表格中的数字转换为文本/单词
问题描述
例子:
价值 | 期望的输出 |
---|---|
300 | 三百 |
300.50 | 三百五十美分 |
我在 Google 表格中找不到执行此操作的方法,而且我还不够精明,无法构建任何东西。
解决方案
确实显示了可以执行此操作的 Excel 公式。我刚刚在 Google 表格中进行了测试,这也有效。
公式是
=IF(OR(LEN(FLOOR(E3,1))>=13,FLOOR(E3,1)<=0),"Out of range",PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(E3>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(E3),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(E3),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(E3,1)>1," dollars"," dollar")))
推荐阅读
- javascript - JavaScript 正在减慢 HTML 渲染速度
- python - 如何使用 Python 找到 FFT 图的峰值?
- python-3.x - 如何在我的 venv 本地运行 App Platform 的 Flask 应用程序?
- android - 在 Play 商店中部署并安装时启动画面卡住
- flutter - 在导航器颤动中传递参数
- firebase - React Native:尝试创建新用户时出现firebase auth / network-request-failed错误,但登录时没有?
- api - Rest API 应该再次验证传入的请求吗?
- apache-spark - 使用 PySpark 在组之后将行收集为 Spark 数据帧的数组
- javascript - ajax 响应将每个值作为单个字符返回
- java - 以连续整数作为键的 HashMap 与 ArrayList