excel 中,比如i2=123456.78（不固定）,如何实现B2=1,C2=2,D2=3,E2=4,F2=5,G2=6,H2=7

Question

excel 中,比如i3=123456.78（不固定）,如何实现B3=1,C3=2,D3=3,E3=4,F3=5,G3=6,H3=7

Answer 1

B2输入

=LEFT(RIGHT(TEXT($I2/1%," 0;-0;"),10-COLUMN()))

右拉到H2，再下拉即可，完全容错并自动屏蔽空单元格。

追问

要数字前面带 Y的  金钱标志  怎么做

追答=LEFT(RIGHT(TEXT($I2/1%," ￥0; -￥0;"),10-COLUMN()))

 

Answer 2

如果一直保留2位小数  则

研究好半天，技术不高，能达到你要求，以下测试过  ok

B2=IFERROR(IF(LEN(I2)>4,LEFT(I2,1),IF(LEN(I2)=4,LEFT(I2,1),"")),"")

 

C2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-1>3,RIGHT(LEFT(I2,2),1),IF(LEN(I2)-1=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-1=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,3),1),"")),"")

 

D2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-2>3,RIGHT(LEFT(I2,3),1),IF(LEN(I2)-2=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-2=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,4),1),"")),"")

 

E2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-3>3,RIGHT(LEFT(I2,4),1),IF(LEN(I2)-3=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-3=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,""),1),"")),"")

 

F2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-4>3,RIGHT(LEFT(I2,5),1),IF(LEN(I2)-4=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-4=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,""),1),"")),"")

 

G2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-5>3,RIGHT(LEFT(I2,6),1),IF(LEN(I2)-5=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-5=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,""),1),"")),"")

 

H2=IFERROR(IF(LEN(I2)>4,IF(LEN(I2)-6>3,RIGHT(LEFT(I2,7),1),IF(LEN(I2)-6=3,LEFT(RIGHT(I2,2),1),IF(LEN(I2)-6=2,RIGHT(I2,1),""))),IF(LEN(I2)=4,RIGHT(LEFT(I2,""),1),"")),"")

Answer 3

=MID($I2/1%,COLUMN(A2),1)
右拉填充追问

在最前面有钱的那个标志 怎么弄呢？就像财务账本上面的

追答

=MID("$"&$I2/1%,COLUMN(A2),1)