第2个回答 2019-08-23
对E7单元格的百万内数字转换为中文大写函数公式如下:
=CONCATENATE(IF(LEN(ROUND(E7*100,0))>8,"¥"&NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),9)),2),IF(LEN(ROUND(E7*100,0))=8,"¥",""))," 佰 ",IF(LEN(ROUND(E7*100,0))>7,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),8)),2),IF(LEN(ROUND(E7*100,0))=7,"¥",""))," 拾 ",IF(LEN(ROUND(E7*100,0))>6,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),7)),2),IF(LEN(ROUND(E7*100,0))=6,"¥",""))," 万 ",IF(LEN(ROUND(E7*100,0))>5,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),6)),2),IF(LEN(ROUND(E7*100,0))=5,"¥",""))," 仟 ",IF(LEN(ROUND(E7*100,0))>4,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),5)),2),IF(LEN(ROUND(E7*100,0))=4,"¥",""))," 佰 ",IF(LEN(ROUND(E7*100,0))>3,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),4)),2),IF(LEN(ROUND(E7*100,0))=3,"¥",""))," 拾 ",IF(LEN(ROUND(E7*100,0))>2,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),3)),2),IF(LEN(ROUND(E7*100,0))=2,"¥",""))," 元 ",IF(LEN(ROUND(E7*100,0))>1,NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),2)),2),IF(LEN(ROUND(E7*100,0))=1,"¥",""))," 角 ",NUMBERSTRING(LEFT(RIGHT(ROUND(E7*100,0),1)),2)," 分 ")