去除多餘的"0"

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2014-7-25 14:58 | 顯示全部帖子

回復 3# Qin
1000080001150
1000080001151
1000080001150T
1000080001151T

只取
1_8_1150
1_8_1151
1_8_1150T
1_8_1151T

又該如何下函數?

公式如下

=LEFT(A1)&"_"&MID(A1,6,1)&"_"&--MID(A1,7,7)&MID(A1,14,1)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2014-7-25 17:02 | 顯示全部帖子

本帖最後由 ML089 於 2014-7-25 17:08 編輯

回復 6# p212

100080001150
10000080001151
1000080001150T
100008001151T
取與5#一樣的結果
1_8_1150
1_8_1151
1_8_1150T
1_8_1151T
又該如何下「通用」公式?
謝謝！

公式如下
=SUBSTITUTE(TRIM(SUBSTITUTE(LEFT(A1,LEN(A1)-3),0," "))," ","_")&RIGHT(A1,3)

公式仍需要看資料結構來判斷，單純的變化就用簡單的公式，所謂的「通用」公式只是考慮更多狀況，不見的是萬能。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2014-7-26 16:29 | 顯示全部帖子

回復 8# Qin

加設最後3個0 ( 000 )前的多數0簡化為 "-"
=SUBSTITUTE(TRIM(SUBSTITUTE(LEFT(A1,3-LOOKUP(,-FIND("000",A1,ROW($1:$19)))),0," "))," ","_")&MID(A1,4-LOOKUP(,-FIND("000",A1,ROW($1:$19))),99)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2014-7-26 16:57 | 顯示全部帖子

本帖最後由 ML089 於 2014-7-26 17:07 編輯

回復 8# Qin

這是另外一解
假設最後一組標籤最長5字
=SUBSTITUTE(TRIM(SUBSTITUTE(LEFT(A1,LEN(A1)-5),0," "))," ","_")&"_"&--LEFT(RIGHT(A1,5),4)&RIGHT(A1)

上式最後一組標籤為1字元時有誤，修改如下，
=SUBSTITUTE(TRIM(SUBSTITUTE(LEFT(A1,LEN(A1)-5),0," "))," ","_")&"_"&TEXT(LEFT(RIGHT(A1,5),4),"0;;")&RIGHT(A1)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2014-8-2 22:57 | 顯示全部帖子

回復 11# jj369963

>>加設最後3個0 ( 000 )前的多數0簡化為 "-"
>>=SUBSTITUTE(TRIM(SUBSTITUTE(LEFT(A1,3-LOOKUP(,-FIND("000",A1,ROW($1:$19)))),0," "))," ","_") & MID(A1,4-LOOKUP(,-FIND("000",A1,ROW($1:$19))),99)

資料格式 123000004560000789

-LOOKUP(,-FIND("000",A1,ROW($1:$19))) 可以找到最後000的位置

取出前後兩段資料
LEFT(A1,3-LOOKUP(,-FIND("000",A1,ROW($1:$19)))) 取出資料 "1230000045600007"
MID(A1,4-LOOKUP(,-FIND("000",A1,ROW($1:$19))),99) 取出資料 "89"

SUBSTITUTE(TRIM(SUBSTITUTE("1230000045600007",0," "))," ","_") 變成 "123_456_7"

"123_456_7" & "89" = "123_456_789"

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式