作者Ryu3y3s (3y3s)
看板Office
標題Re: [問題] google sheet 數字轉中文大寫
時間Mon Apr 4 19:04:53 2022
※ 引述《elrice2010 (玉米貓)》之銘言：
: 參考W大的公式做了一點修改，測試都沒問題，提供給大家~
: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B31,"0
: 億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍
: "),"6","陸"),"7","柒"),"8","捌"),"9","玖"),"0","零")
: 感謝W大拯救了我的肝。
: 請收下我的膝蓋。
: ※ 引述《windknife18 (windknife18)》之銘言：
: : 第一次玩 Google Sheet，可以用以下暴力法來解
: : =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUB
: : STITUTE(SUBSTITUTE(substitue(SUBSTITUTE(TEXT(A2,"[DBNUM2]0
: : 億0仟萬0佰萬0拾萬0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆
: : "),"5","伍"),"6","路"),"7","柒"),"8","捌"),"9","玖"),"0","零")
根據 e大 和 w大 的式子寫程式生成了比較接近一般中文用法的版本
例如 10001 會變成 新臺幣壹萬零壹元整
https://pastebin.com/rxzW0hR1
將裡面的 F44 替換成要用的儲存格就好
如果有 bug 煩請告知一下
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.229.232.223 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1649070296.A.73B.html
1F：推 newacc: 4657個字元的公式必須推XD 04/05 21:38