本帖最后由 cutecpu 于 2019-11-3 15:15 编辑
B1 陣列公式: =
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TEXJOIN(,,
IF(
ISERR(--MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),".","0"),ROW($1:$40),1)),
MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),".","0"),ROW($1:$40),1),""
)&
IF(
ISERR(--MID(SUBSTITUTE(SUBSTITUTE("$"&A1,CHAR(10)," "),".","0"),ROW($1:$40),1))*
ISNUMBER(--MID(SUBSTITUTE(A1,CHAR(10)," "),ROW($1:$40),1)),
TEXT(MID(SUBSTITUTE(A1,CHAR(10)," "),ROW($1:$40),5),"00000.###"&CHAR(10)),""
)
)
,"."&char(10),char(10))," ","")&"$",char(10)&"$","")
說明:
藍色: 文本
橙色: 數字(整數補滿5位)
紅色: 去除多餘小數點、空白、結尾空行
: 有 4 格空白
祝順心,南無阿彌陀佛!
|