去除多餘的&quot;0&quot;

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

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

12^#

發表於 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 三鍵輸入公式

jj369963

中學生 Rank: 2

帖子: 147
主題: 46
精華: 0
積分: 205
點名: 0
作業系統: windows xp
軟體版本: office 2003
閱讀權限: 20
性別: 女
註冊時間: 2012-3-19
最後登錄: 2021-5-21

11^#

發表於 2014-8-2 21:52 | 只看該作者

回復 9# ML089

Dear ML089版主：
感謝你提供的精材文字函數運用，但學識淺薄的我，實在看不懂這函數可否解說下，感謝

加設最後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)

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

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

10^#

發表於 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猴年全勤獎章

9^#

發表於 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 三鍵輸入公式

Qin

中學生 Rank: 2

帖子: 207
主題: 61
精華: 0
積分: 270
點名: 0
作業系統: Vista
軟體版本: office2003
閱讀權限: 20
性別: 女
註冊時間: 2011-3-20
最後登錄: 2023-6-25

8^#

發表於 2014-7-26 14:58 | 只看該作者

回復 7# ML089

請教前輩, 如果資料如下:
100000000006453
110000000000729
M00000000000010
H00000000001160
150000000000706
1000090000100
1000090000101
1000090000100T
1000090000101T
1000085000320
1000085000320T
1000085000321
1000085000321T
100000080001091
100000080001101

要如何取出以下的答案?
1_6453
11_729
M_10
H_1160
15_706
1_9_100
1_9_101
1_9_100T
1_9_101T
1_85_320
1_85_320T
1_85_321
1_85_321T
1_8_1091
1_8_1101

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

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

7^#

發表於 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 三鍵輸入公式

p212

高中生 Rank: 3 Rank: 3

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

6^#

發表於 2014-7-25 15:35 | 只看該作者

本帖最後由 p212 於 2014-7-25 15:42 編輯

回復 5# ML089
請教ML089版主
如果資料如下
100080001150
10000080001151
1000080001150T
100008001151T
取與5#一樣的結果
1_8_1150
1_8_1151
1_8_1150T
1_8_1151T
又該如何下「通用」公式?
謝謝！

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

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

5^#

發表於 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猴年全勤獎章

4^#

發表於 2014-7-25 14:54 | 只看該作者

回復 1# Qin

100000000006453
100000000000727
100000000000729
100000000000436
100000000000426
100000000000706
300000000017830
請問要如何去除里面的"0"

公式如下

=LEFT(A1)&"_"&--RIGHT(A1,14)

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