Editing Convert number to chinese words

將數字轉換成中文國字大寫

== Convert number to Chinese words in Excel ==

<pre>
=TEXT(A1,"[DBNUM2]0億0仟萬0佰萬0拾萬0萬0仟0佰0拾0元")
</pre>

== Convert number to Chinese words in Google sheet ==
Source of formula: PTT<ref>[https://www.ptt.cc/bbs/Office/M.1644921987.A.FA8.html Re: (問題) google sheet 數字轉中文大寫 - 看板 Office - 批踢踢實業坊]</ref>
<pre>
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,"0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌"),"9","玖"),"0","零")
</pre>

Apps script version
<pre>
function NUMBER2HANS(input) {

  let numbers = '0123456789'
  let hans = '零壹貳參肆伍陸柒捌玖'
  const hans_digits = ['元', '拾', '佰', '仟', '萬', '拾萬', '佰萬', '仟萬', '億']
  //input = input.trim()
  //input = input.replace(/^\s+|\s+$/gm,'');
  let length = input.length;
  //console.log("length: " + length);

  // javascript - How can I pad a value with leading zeros? - Stack Overflow https://stackoverflow.com/questions/1267283/how-can-i-pad-a-value-with-leading-zeros
  maxLength = 9; // maxLength is the max string length, not max # of fills
  input = input.toString().padStart(maxLength, "0");
  length = input.length;

  let output = ''
  for (let i = 0; i < input.length; i++) {
    let currentDigit = length - i - 1;
    //console.log("currentDigit: " + currentDigit);
    let currentHansDigit = hans_digits[currentDigit];
    //console.log("currentHansDigit: " + currentHansDigit);
      
    let currentChar = input.charAt(i) 
    //console.log("currentChar: " + currentChar);
    
    let currentHans = hans.charAt(currentChar) 
    //console.log("currentHans: " + currentHans);
    //let output = currentHans + currentHansDigit
    output += currentHans + currentHansDigit
    //console.log("output: " + output); 
  }

  return output
}

</pre>

== Related ==
* [https://chinese.gratis/tools/chinesenumbers/ Chinese Number Conversion (Online Converter) | Chinese Gratis] in simplified Chinese
* [https://github.com/digi3studio/number-to-chinese-words digi3studio/number-to-chinese-words: Convert a number to chinese words. 由數字轉為中文數目]
* [https://stackoverflow.com/questions/71138491/how-can-i-replace-multiple-string-at-once-in-excel How can I replace multiple string at once in Excel? - Stack Overflow]
* [https://www.ablebits.com/office-addins-blog/2021/06/30/excel-find-replace-multiple-values/ Excel: find and replace multiple values at once - Ablebits.com]

== References ==
<references />


[[Category:Spreadsheet]] [[Category:Excel]]
[[Category:Data Science]]
[[Category:String manipulation]]