Преобразование чисел в Google Sheets в текст / слова

Question

Преобразование чисел в Google Sheets в текст / слова

Пример:

value  | desired output
-------+----------------------------
300    | Three Hundred
300.50 | Three Hundred and 50 cents

Я не могу найти способ сделать это в Google Sheets, и я не достаточно сообразителен, чтобы что-то построить.

7

google-sheets google-sheets-formula google-sheets-macros

Источник

user11102488 22 фев '19 в 16:10

8 ответов

Другие вопросы по тегам google-sheets google-sheets-formula google-sheets-macros

user9099497 22 фев '19 в 16:35 2019-02-22 16:35 · Answer 1 · 2019-02-22 16:35

Этот веб-сайт https://www.excelforum.com/tips-and-tutorials/1015010-convert-a-numeric-value-to-words-without-vba.html

Показывает ли формула Excel, которая может сделать это. Я только что проверил в Google Sheets, и это тоже работает.

Формула

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

user5632629 22 фев '19 в 18:52 2019-02-22 18:52 · Answer 2 · 2019-02-22 18:52

небольшие изменения в Dominic, чтобы удовлетворить ваши потребности именно так, как вы хотите:

=REGEXREPLACE(IF(OR(LEN(FLOOR(A1,1))>=13,FLOOR(A1,1)<=0),"Out of range",
 PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
 IF(A1>=10^9," billion ",""),
 CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),
 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(A1),
 REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),
 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(A1), 
 REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
 IF(VALUE(MID(TEXT(INT(A1),
 REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),
 REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),""))),"  "," "))&
 IF(IFERROR(QUERY({A1},"where Col1 Contains '.'"),)<>"",
 " and "&REGEXEXTRACT(TO_TEXT(A1), "\.(.+)")&" cents", )),"  ", " ")

user995714 10 окт '19 в 20:23 2019-10-10 20:23 · Answer 3 · 2019-10-10 20:23

Поскольку это специально для Google Sheet, вы можете использовать это

=REGEXEXTRACT(JOIN(", ",QUERY(IMPORTDATA("https://www.calculatorsoup.com/calculators/conversions/numberstowords.php?number=" & A1 & "&format=words&letter_case=lowercase&action=solve"),"where Col1 contains '<div id=""answer""><br>'")),">([^<]+)")

где A1 - это число, которое вы хотите преобразовать

Кредит: формулы чисел по буквам или числа в слова. В этой ссылке также есть много альтернативных решений, например, здесь способ, который использует формулу массива

=ArrayFormula(if(A2:A="","",if(len(int(A2:A))<13,"",if(right(left(int(A2:A),len(int(A2:A))-12),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-12),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-12),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-12),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-12),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-12),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" trillion")&" "&if(len(int(A2:A))<10,"",if(right(left(int(A2:A),len(int(A2:A))-9),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-9),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-9),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-9),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-9),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-9),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" billion")&" "&if(len(int(A2:A))<7,"",if(right(left(int(A2:A),len(int(A2:A))-6),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-6),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-6),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-6),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-6),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-6),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" million")&" "&if(len(int(A2:A))<4,"",if(right(left(int(A2:A),len(int(A2:A))-3),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-3),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-3),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-3),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-3),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-3),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" thousand")&" "&if(len(int(A2:A))<1,"",if(right(left(int(A2:A),len(int(A2:A))-0),3)+0>99,choose(left(right(left(int(A2:A),len(int(A2:A))-0),3))+0,"one","two","three","four","five","six","seven","eight","nine")&" hundred ",)&if(right(right(left(int(A2:A),len(int(A2:A))-0),3),2)*1>19,choose(left(right(right(left(int(A2:A),len(int(A2:A))-0),3),2))-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(right(left(int(A2:A),len(int(A2:A))-0),3))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(right(right(left(int(A2:A),len(int(A2:A))-0),3),2)+1,"","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")))&if(int(A2:A)=0,"0 dollar",if(int(A2:A)<2," dollar"," dollars"))&iferror(" and "&if(round(mod(A2:A,1)*100,2)>19,choose(left(mod(A2:A,1)*100)-1,"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&choose(right(round(mod(A2:A,1)*100,2))+1,""," one"," two"," three"," four"," five"," six"," seven"," eight"," nine"),choose(round(mod(A2:A,1)*100,2)+1,"no","one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"))&" cents")))

На выходе

300         three hundred  dollars and no cents
300.5       three hundred  dollars and fifty cents

Просто измените A2:A в формуле массива в правильную позицию, если ваши числа находятся в другой позиции

Другой способ - клонировать 2 уже созданных ниже листа, выбрав Файл> Создать копию, а затем использовать

Кредит: Конвертер количества или чисел в слова с помощью Google Таблиц. Автор также представил более новые версии, для которых нужен только один лист. Если вы хотите использовать их, клонируйте его отсюда

user1393023 10 окт '19 в 20:03 2019-10-10 20:03 · Answer 4 · 2019-10-10 20:03

Вы можете попробовать надстройку Numbertext

=NUMBERTEXT("EUR 25") => twenty-five euro

=MONEYTEXT(25,"EUR") => twenty-five euro

2

Источник

user1393023 10 окт '19 в 20:03

user5632629 22 фев '19 в 16:21 2019-02-22 16:21 · Answer 5 · 2019-02-22 16:21

сделайте копию этого файла: конвертер
а затем либо добавьте эти два листа в электронную таблицу, либо используйте IMPORTRANGE импортировать значение в F6 и другой IMPORTRANGE импортировать обратно преобразованное значение из B11

0

Источник

user5632629 22 фев '19 в 16:21

user3779350 07 апр '20 в 12:15 2020-04-07 12:15 · Answer 6 · 2020-04-07 12:15

Вы можете использовать этот метод, чтобы конвертировать валюту в слова:

function convertToWords(input) {

  var a, b, c, d, e, output, outputA, outputB, outputC, outputD, outputE;

  var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];

  if (input === 0) { // Zero

    output = "Rupees zero";

  } else if (input == 1) { // One

    output = "Rupee one only";

  } else { // More than one

    // Tens
    a = input % 100;
    outputA = oneToHundred_(a);

    // Hundreds
    b = Math.floor((input % 1000) / 100);
    if (b > 0 && b < 10) {
      outputB = ones[b];
    }

    // Thousands
    c = (Math.floor(input / 1000)) % 100;
    outputC = oneToHundred_(c);

    // Lakh
    d = (Math.floor(input / 100000)) % 100;
    outputD = oneToHundred_(d);

    // Crore
    e = (Math.floor(input / 10000000)) % 100;
    outputE = oneToHundred_(e);

    // Make string
    output = "Rupees";

    if (e > 0) {
      output = output + " " + outputE + " crore";
    }

    if (d > 0) {
      output = output + " " + outputD + " lakh";
    }

    if (c > 0) {
      output = output + " " + outputC + " thousand";
    }

    if (b > 0) {
      output = output + " " + outputB + " hundred";
    }

    if (input > 100 && a > 0) {
      output = output + " and";
    }

    if (a > 0) {
      output = output + " " + outputA;
    }

    output = output + " only";
  }

  return output;

}

function oneToHundred_(num) {

  var outNum;

  var ones = ['', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];

  var teens = ['ten', 'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'];

  var tens = ['', '', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety'];

  if (num > 0 && num < 10) { // 1 to 9

    outNum = ones[num]; // ones

  } else if (num > 9 && num < 20) { // 10 to 19

    outNum = teens[(num % 10)]; // teens

  } else if (num > 19 && num < 100) { // 20 to 100

    outNum = tens[Math.floor(num / 10)]; // tens

    if (num % 10 > 0) {

      outNum = outNum + " " + ones[num % 10]; // tens + ones

    }

  }

  return outNum;

}

29 май '21 в 11:10 2021-05-29 11:10 · Answer 7 · 2021-05-29 11:10

Мне нравится формула массива @phuclv, потому что в ней есть слово «доллары», однако, когда я попытался ее использовать, я заметил, что центы преобразуются в текст только до 19 центов. Если значение составляет от 20 до 99 центов, это не так.

Чего не хватает, проверьте снимок экрана:

введите описание изображения здесь

user5372400 15 окт '21 в 09:00 2021-10-15 09:00 · Answer 8 · 2021-10-15 09:00

Пожалуйста, попробуй:

=GOOGLETRANSLATE(BAHTTEXT(7245.01),"th","en")

Результат

Семь тысяч двести сорок пять бат, один сатанг

Затем используйте регулярное выражение или текстовую функцию, чтобы избавиться / заменить это:

бат → в $ или валюту
сатанг → в центы или другое.

Вот список языковых кодов для перевода:

https://www.loc.gov/standards/iso639-2/php/code_list.php