excel中怎样将小写金额自动填到万千百十元角分十进位的格式中

Question

如题所述

Answer 1

例：小写金额在A1中，可以在A2中（英文状态）：输入以下公式
=TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(IF(LEFT(RIGHTB(A1,3),1)=".",LEFT(RIGHTB(A1,2),1),IF(LEFT(RIGHTB(A1,2),1)=".",LEFT(RIGHTB(A1,1),1),0)),"[dbnum2]")&"角"&TEXT(IF(LEFT(RIGHTB(A1,3),1)=".",RIGHT(A1),0),"[dbnum2]")&"分"

把公式直接复制到excel中，把公式中的“A1”修改成你想要的就可以了。

Answer 2

为了避免公式过长，引入B1和C1做为辅助。

A2为待转换的数值。
B2=A2*100，作用是去除小数点。
C2=LEN(B2)，作用是计算字符串的长度。
分位：=IF($B$2=0,"",IF($C$2=1,$B$2,IF($C$2>1,LEFT(RIGHTB($B$2,1),1),"")))
角位：=IF($B$2=0,"",IF($C$2=1,"￥",IF($C$2>1,LEFT(RIGHTB($B$2,2),1),"")))
元位：=IF($C$2=2,"￥",IF($C$2>2,LEFT(RIGHTB($B$2,3),1),""))
十位：=IF($C$2=3,"￥",IF($C$2>3,LEFT(RIGHTB($B$2,4),1),""))
百位：=IF($C$2=4,"￥",IF($C$2>4,LEFT(RIGHTB($B$2,5),1),""))
千位：=IF($C$2=5,"￥",IF($C$2>5,LEFT(RIGHTB($B$2,6),1),""))
万位：=IF($C$2=6,"￥",IF($C$2>6,LEFT(RIGHTB($B$2,7),1),""))
十万位：=IF($C$2=7,"￥",IF($C$2>7,LEFT(RIGHTB($B$2,8),1),""))
百万位：=IF($C$2=8,"￥",IF($C$2>8,LEFT(RIGHTB($B$2,9),1),""))
千万位：=IF($C$2=9,"￥",IF($C$2>9,LEFT(RIGHTB($B$2,10),1),""))
亿位：=IF($C$2=10,"￥",IF($C$2>10,LEFT(RIGHTB($B$2,11),1),""))

Answer 3

比如我用10000在单元格中，10000"即可