excel - 将 Excel 值转换为单词(英文)-6ren

excel - 将 Excel 值转换为单词(英文)

转载作者：行者123 更新时间：2023-12-04 22:08:29

我发现这个公式可以将金额转换为单词，但它不能 100% 起作用。每当金额以 20,30, 40 等结尾时，都会出现错误。例如 1020 会出错，但 1019 和 1021 可以正常工作。我试图自己解决这个问题，但没有运气。它必须是公式(与之交互的软件(Google Docs)无法处理 VBA 脚本。

这是公式:

=IF(H53<10^12,
    IF(INT(H53/10^9)>0,
        IF(INT(H53/10^11)>0,
            INDEX(
                {"One ","Two ","Three","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(INT(H53/10^11),{1,2,3,4,5,6,7,8,9})
            )&"Hundred ",
        "")&
        IF(MOD(INT(H53/10^9),100)<20,
            INDEX(
                {"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ","Ten ",
                "Eleven ","Twelve","Thirteen","Fourteen ","Fifteen","Sixteen","Seventeen","Eighteen ","Nineteen"},
                MATCH(MOD(INT(H53/10^9),100),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19})
            ),
            INDEX(
                {"","Twenty ","Thirty ","Fourty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety "}, 
                MATCH(INT(MOD(INT(H53/10^9),100)/10),{1,2,3,4,5,6,7,8,9})
            )&
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "}, 
                MATCH(MOD(INT(H53/10^9),10),{1,2,3,4,5,6,7,8,9})
            )
        )&" Billion  ",
        ""
    )&
    IF(MOD(INT(H53/10^6),10^3)>0,
        IF(INT(MOD(INT(H53/10^6),10^3)/100)>0,
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(INT(MOD(INT(H53/10^6),10^3)/100),{1,2,3,4,5,6,7,8,9})
            )&"Hundred ",
        "")&
        IF(MOD(INT(H53/10^6),100)<20,
            INDEX(
                {"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ","Ten ",
                "Eleven ","Twelve","Thirteen","Fourteen ","Fifteen ","Sixteen","Seventeen","Eighteen ","Nineteen"},
                MATCH(MOD(INT(H53/10^6),100),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19})
            ),
            INDEX(
                {"","Twenty ","Thirty ","Fourty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety "},
                MATCH(INT(MOD(INT(H53/10^6),100)/10),{1,2,3,4,5,6,7,8,9})
            )&
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "}, 
                MATCH(MOD(INT(H53/10^6),10),{1,2,3,4,5,6,7,8,9})
            )
        )&" Million  ",
        ""
    )&
    IF(MOD(INT(H53/10^3),10^3)>0,
        IF(INT(MOD(INT(H53/10^3),10^3)/100)>0,
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(INT(MOD(INT(H53/10^3),10^3)/100),{1,2,3,4,5,6,7,8,9})
            )&"Hundred ",
        "")&
        IF(MOD(INT(H53/10^3),100)<20,
            INDEX(
                {"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ","Ten ",
                "Eleven ","Twelve","Thirteen","Fourteen ","Fifteen ","Sixteen","Seventeen","Eighteen ","Nineteen"},
                MATCH(MOD(INT(H53/10^3),100),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19})
            ),
            INDEX(
                {"","Twenty ","Thirty ","Fourty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety "},
                MATCH(INT(MOD(INT(H53/10^3),100)/10),{1,2,3,4,5,6,7,8,9})
            )&
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(MOD(INT(H53/10^3),10),{1,2,3,4,5,6,7,8,9})
            )
        )&" Thousand  ",
        ""
    )&
    IF(MOD(MOD(H53,10^4),10^3)>0,
        IF(INT(MOD(MOD(H53,10^4),10^3)/100)>0,
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(INT(MOD(MOD(H53,10^4),10^3)/100),{1,2,3,4,5,6,7,8,9})
            )&"Hundred ",
            ""
        )&
        IF(MOD(MOD(MOD(H53,10^4),10^3),100)<20,
            INDEX(
                {"","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ","Ten ",
                "Eleven ","Twelve","Thirteen","Fourteen ","Fifteen ","Sixteen","Seventeen","Eighteen ","Nineteen"},
                MATCH(MOD(MOD(MOD(H53,10^4),10^3),100),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19})
            ),
            INDEX(
                {"","Twenty ","Thirty ","Fourty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety "},
                MATCH(INT(MOD(MOD(MOD(H53,10^4),10^3),100)/10),{1,2,3,4,5,6,7,8,9})
            )&
            INDEX(
                {"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
                MATCH(MOD(MOD(MOD(H53,10^4),10^3),10),{1,2,3,4,5,6,7,8,9})
            )
        ),
        ""
    ),
    ""
) & " Rands"

最佳答案

问题出在最后一部分:

 INDEX({"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
     MATCH(MOD(MOD(MOD(H53,10^4),10^3),10),{1,2,3,4,5,6,7,8,9})

MOD(MOD(MOD(H53,10^4),10^3),10)将返回 0 ，在1-9中没有找到。

因此，上述行应修改为:

 INDEX({"", "One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "},
     MATCH(MOD(MOD(MOD(H53,10^4),10^3),10),{0, 1,2,3,4,5,6,7,8,9})

这个问题也存在于公式的其他领域。

所有在这个完整的公式中都是固定的(它还添加了一些不一致地遗漏的空格):

=IF(B1<10^12,
 IF(INT(B1/10^9)>0,
  IF(INT(B1/10^11)>0,
   INDEX(
    {"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(INT(B1/10^11),{1;2;3;4;5;6;7;8;9})
   )&"Hundred ",
  "")&
  IF(MOD(INT(B1/10^9),100)<20,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen "},
    MATCH(MOD(INT(B1/10^9),100),{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19})
   ),
   INDEX(
    {"";"Twenty ";"Thirty ";"Fourty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety "},
    MATCH(INT(MOD(INT(B1/10^9),100)/10),{1;2;3;4;5;6;7;8;9})
   )&
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(MOD(INT(B1/10^9),10),{0;1;2;3;4;5;6;7;8;9})
   )
  )&" Billion  ",
  ""
 )&
 IF(MOD(INT(B1/10^6),10^3)>0,
  IF(INT(MOD(INT(B1/10^6),10^3)/100)>0,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(INT(MOD(INT(B1/10^6),10^3)/100),{0;1;2;3;4;5;6;7;8;9})
   )&"Hundred ",
  "")&
  IF(MOD(INT(B1/10^6),100)<20,
   INDEX(
   {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen "},
    MATCH(MOD(INT(B1/10^6),100),{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19})
   ),
   INDEX(
    {"";"Twenty ";"Thirty ";"Fourty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety "},
    MATCH(INT(MOD(INT(B1/10^6),100)/10),{1;2;3;4;5;6;7;8;9})
   )&
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(MOD(INT(B1/10^6),10),{0;1;2;3;4;5;6;7;8;9})
   )
  )&" Million  ",
  ""
 )&
 IF(MOD(INT(B1/10^3),10^3)>0,
  IF(INT(MOD(INT(B1/10^3),10^3)/100)>0,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(INT(MOD(INT(B1/10^3),10^3)/100),{0;1;2;3;4;5;6;7;8;9})
   )&"Hundred ",
  "")&
  IF(MOD(INT(B1/10^3),100)<20,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen "},
    MATCH(MOD(INT(B1/10^3),100),{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19})
   ),
   INDEX(
    {"";"Twenty ";"Thirty ";"Fourty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety "},
    MATCH(INT(MOD(INT(B1/10^3),100)/10),{1;2;3;4;5;6;7;8;9})
   )&
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(MOD(INT(B1/10^3),10),{0;1;2;3;4;5;6;7;8;9})
   )
  )&" Thousand  ",
  ""
 )&
 IF(MOD(MOD(B1,10^4),10^3)>0,
  IF(INT(MOD(MOD(B1,10^4),10^3)/100)>0,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(INT(MOD(MOD(B1,10^4),10^3)/100),{0;1;2;3;4;5;6;7;8;9})
   )&"Hundred ",
   ""
  )&
  IF(MOD(MOD(MOD(B1,10^4),10^3),100)<20,
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine ";"Ten ";"Eleven ";"Twelve ";"Thirteen ";"Fourteen ";"Fifteen ";"Sixteen ";"Seventeen ";"Eighteen ";"Nineteen "},
    MATCH(MOD(MOD(MOD(B1,10^4),10^3),100),{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19})
   ),
   INDEX(
    {"";"Twenty ";"Thirty ";"Fourty ";"Fifty ";"Sixty ";"Seventy ";"Eighty ";"Ninety "},
    MATCH(INT(MOD(MOD(MOD(B1,10^4),10^3),100)/10),{1;2;3;4;5;6;7;8;9})
   )&
   INDEX(
    {"";"One ";"Two ";"Three ";"Four ";"Five ";"Six ";"Seven ";"Eight ";"Nine "},
    MATCH(MOD(MOD(MOD(B1,10^4),10^3),10),{0;1;2;3;4;5;6;7;8;9})
   )
  ),
  ""
 ),
 ""
) & " Rands"

关于excel - 将 Excel 值转换为单词(英文)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/15065644/

文章推荐： sql - 黑斑羚查询 : Find value in pipe-separated list

文章推荐： wcf - 派生类型的 Nservicebus 序列化问题

文章推荐： excel - 从excel单元格中获取子字符串

数据框列中的Python计数字符串(单词)
我有以下数据框 (df_hvl)，列名“FzListe”和以下数据: FzListe 7MA1, 7OS1 7MA1, 7ZJB 7MA2, 7MA3, 7OS1 76G1, 7MA1, 7OS1 7
在写入文件之前检查文件中是否存在字符串/单词
我有点小问题。仅当尝试写入的相同字符串/单词不存在时，我才想写入文件。在我的例子中，它是一个 IP 地址和端口，用“:”分隔。如果我手动写入文件，例如 193...:80 和 193...:22，它指
正则表达式从逗号之间的最后一个单词中提取(完全匹配)单词？
如何返回结果列中的单词示例？我得到的最接近的是 [\W]{2,}[^,\W].+[?=,] ID 文本我的结果(完全匹配) 预期(完全匹配) 1 词A，世界B，词C , 世界 B, 字B 2 wo
c - 正则表达式以获取英文引号之间的字符串/单词
我想在引号之间得到一个字符串我知道一个解决方案是: /'.*?'/ 但问题是它不适用于英语中的所有格或收缩格例如: What is the name of Mario's brother in t
字符串中出现次数最多的 PHP 单词
我应该在句子中找到出现最多的单词。这是我尝试过的，但不起作用。 '); $max = -1; $resultWords = array(); $resultCount = array(); $i =
Vim高亮显示用于复制到剪贴板的字符/单词/行
我是vim的新手。我正在尝试练习(最近阅读了一些教程)，但是我发现我不能不突出显示“复制粘贴”中的字符/单词/行。在Textmate中，我通常使用SHIFT + CTRL + LeftArrowKe
json - 单词，单词类型和定义的英语JSON词典
有谁知道一个JSON格式的英语词典，该词典具有(单词，定义和单词类型，例如名词/形容词/动词/副词) 这种格式: [ {"Word" : "Chair", "Definition" : "A
Javascript - 无法替换字符串中的 "<"单词
我正在做一些 javascript，同时我注意到我无法替换 html 标记内的“ document.getElementById('label').innerHTML = document.get
groovy - 如何在groovy中替换文本文件中的字符串/单词
您好，我正在使用 groovy 2.1.5，我必须编写一个代码来显示具有给定路径的目录的内容/文件，然后它会备份文件并替换文件中的单词/字符串。这是我用来尝试替换所选文件中的单词的代码 String
python - 用python机器学习识别声音(单词)
我正在准备一个实验，我想使用python编写程序以识别参与者说出的某些单词。我在python中搜索了很多有关语音识别的内容，但结果却很复杂(例如CMUSphinx)。我要实现的是一个程序，该程序接
powershell - 如何忽略字母/单词？
假设我有以下代码: $size = 23.9 $size = "$size GB" write $size 我想在其他事情上使用相同的变量，即 if ($size -lt 20) {...} 这显然是
regex - 单词/字符的选择性正则表达式
我想替换字符串中单词 Date 的所有情况，除非它是 Date()(即 Date 后跟括号)。这是一个字符串示例以及我最初尝试的内容: x gsub("Date", paste("Date:", S
java - 寻找最小的字符串/单词
我对 Java 和编程都很陌生，请记住这一点，请不要对我严厉 ^^。接下来，我最近用 Java 进行了一些培训，我喜欢这个挑战，但现在我只是陷入困境。我做了一些示例来查找用户输入的最大字符串，一切都很
c - 找到出现次数较多的字符串(单词)
我必须给一个数字x，并写x个字符串(单词)。我必须找到写得最多的那一篇。它可以工作，但是当我尝试从文件中读取它时，却没有。例如，如果我执行 a.out'' #include #include in
javascript - 在java脚本的字符串中引用单个字符(单词)
这里是学习者，如果这个问题看起来很荒谬，请多多包涵。假设我试图引用字符串中的字符而不是字符串本身，我该怎么做呢？我的意思是; 给定:var str = "我想知道一个大脑分散的计算机如何保持理智" 我
java - 如何解析此输出并分隔每个字段/单词
这是阿克沙塔。我一直在解析以下数据。我想单独获取每个单词。我可以有一个示例代码以便我可以继续吗 RTRV-HDR RH01 SIMULATOR 09-11-18 16 13 19 M R
java - 如何拆分字符串以仅获取字符/单词
我有一个任意字符串，它总是包含至少一个英文单词后跟一系列数字:"Hello World 1234" 我如何只提取 "Hello World" 来自字符串？最佳答案在我看来你需要反正则表达式: St
java - 如何从字符串中提取子字符串(单词)？
我正在尝试输入一个四个单词的句子，然后能够使用indexOf和子字符串单独打印出每个单词。有什么想法我做错了吗？已编辑那么这就是它应该的样子吗？我已经运行了两次，得到了两个不同的答案，所以我不确定
php - 如何在文本开头查找短语(单词)
如何在文本开头查找短语(单词) 我需要非常快速的解决方案来查明文本是否以某些已知短语开头我在 Mysql (innodb) 表中的短语如下: CREATE TABLE IF NOT EXISTS `
mysql - 选择包含某些字符的MYSQL数据(单词)
我在 MYSQL 表中有一本字典，该表由 240 000 个单词组成。例如，如果我有字母 G、I、G、S、N> 和 O 我想选择表中包含所有或部分这些字母(并且没有其他字母)的所有单词。可接受的词语

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

excel - 将 Excel 值转换为单词(英文)