123
返回列表 上一主題 發帖

[發問] 提取編號中的數字&數字補零

Hsieh

帖子
4901 
主題
44 
精華
24 
積分
4916 
點名
253  
作業系統
Windows 7 
軟體版本
Office 20xx 
閱讀權限
150 
性別
男 
來自
台北 
註冊時間
2010-4-30 
最後登錄
2024-11-22 

2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱心公益勳章2011兔年全勤獎章
21# 跳轉到 »
發表於 2017-3-22 23:22 | 只看該作者
回復 8# Qin

=SUBSTITUTE(A1,"-",REPT(0,15-LEN(A1)+1))
學海無涯_不恥下問

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
22#
發表於 2017-3-23 09:07 | 只看該作者
本帖最後由 ML089 於 2017-3-23 09:21 編輯

回復 20# Qin

加上IF判斷 D欄是否有值，無值時為空白

{ =IF(D2="","",VLOOKUP(SUBSTITUTE(D2,"-","*"),IF({1,0},A$2:A$99&"",B$2:B$99),2,)) }

A欄資料為 Table_Array 的 Index-key 最好能是固定長度，統一為文字，對查詢比較容易。
目前 A欄 有文字、數字型態，Index-key長短不一致，
雖然 可以用  前字串 + "*" + 後字串 來查詢，但也有可能會有誤判情況

例如
A1*123查詢

下列都可以符合，會優先選取第一個
A10000123
A12000123
A1A123123
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Qin

帖子
207 
主題
61 
精華
0 
積分
270 
點名
0  
作業系統
Vista 
軟體版本
office2003 
閱讀權限
20 
性別
女 
註冊時間
2011-3-20 
最後登錄
2023-6-25 
23#
發表於 2017-3-23 20:50 | 只看該作者
回復 22# ML089

.謝謝!

受教了.

TOP

Andy2483
　暱稱: 厚臉皮的學生

大學生Rank: 4

帖子
1446 
主題
40 
精華
0 
積分
1470 
點名
0  
作業系統
Windows 7 
軟體版本
Excel 2010 & 2016 
閱讀權限
50 
性別
男 
來自
台灣 
註冊時間
2020-7-15 
最後登錄
2024-10-21 
24#
發表於 2023-3-10 15:40 | 只看該作者
本帖最後由 Andy2483 於 2023-3-10 15:57 編輯

回復 19# ML089
回復 1# cdkee


    論壇到處都有寶藏,謝謝論壇,謝謝前輩
後學練習字典與陣列的方法,請前輩再指導

執行前:


執行結果:


Option Explicit
Sub TEST_1()
Dim Brr, Crr, i&, T$, V%, Y, Z$, j%
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([B2], Cells(Rows.Count, "A").End(3))
Crr = Range([E2], Cells(Rows.Count, "E").End(3))
For i = 1 To UBound(Brr)
   T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
   V = Len(T)
   For j = V To 1 Step -1
      If Mid(T, j, 1) <> "0" Then
         Z = Mid(T, j + 1): T = Mid(T, 1, j)
         Exit For
      End If
   Next
   T = Replace(UCase(Trim(Brr(i, 1))), "0", "") & Z
   If Y.Exists(T) = Empty Then
      Y(T) = Brr(i, 2)
      ElseIf Y(T) <> Brr(i, 2) Then
         MsgBox Brr(i, 1) & " 去0簡化後的資料欄有同編號不同商品疑慮"
         Exit Sub
   End If
i01:
Next
For i = 1 To UBound(Crr)
   T = Trim(Crr(i, 1)): If T = "" Then GoTo i02
   V = Len(T)
   For j = V To 1 Step -1
      If Mid(T, j, 1) <> "0" Then
         Z = Mid(T, j + 1): T = Mid(T, 1, j)
         Exit For
      End If
   Next
   T = Replace(UCase(Trim(Crr(i, 1))), "0", "") & Z
   Crr(i, 1) = Y(T)
i02:
Next
[I2].Resize(UBound(Crr), 1) = Crr
Erase Brr, Brr: Set Y = Nothing
End Sub
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

Andy2483
　暱稱: 厚臉皮的學生

大學生Rank: 4

帖子
1446 
主題
40 
精華
0 
積分
1470 
點名
0  
作業系統
Windows 7 
軟體版本
Excel 2010 & 2016 
閱讀權限
50 
性別
男 
來自
台灣 
註冊時間
2020-7-15 
最後登錄
2024-10-21 
25#
發表於 2023-3-13 10:36 | 只看該作者
本帖最後由 Andy2483 於 2023-3-13 10:42 編輯

回復 24# Andy2483


    謝謝論壇,謝謝各位前輩
今天複習此題,發現闕漏與贅述,心得註解如下

Option Explicit
Sub TEST_1()
Dim Brr, Crr, Y, T$, Z$, j%, V%, i&
'↑宣告變數:(Brr,Crr,Y)是通用型變數,(T,Z)是字串變數,
'(j,V)是短整數變數,i是長整數變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y這通用型變數是 字典
Brr = Range([B2], Cells(Rows.Count, "A").End(3))
'↑令Brr是二維陣列,以[B2]到 A欄最後有內容儲存格,這兩格之間所有儲存格值帶入
Crr = Range([E2], Cells(Rows.Count, "E").End(3))
'↑令Brr是二維陣列,以[E2]到 E欄最後有內容儲存格,這兩格之間所有儲存格值帶入
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1 到Brr陣列縱向最大索引列號
   T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
   '↑令T這字串變數是i迴圈列第1欄Brr陣列值去除前後空白字元露的新字:
   '如果T變數是空字元!就跳到 i01位置繼續執行
   V = Len(T)
   '↑令V這短整數變數是 T變數的字元數
   For j = V To 1 Step -1
   '↑設順迴圈!j從V變數到 1,令每個迴圈j都要-1
      If Mid(T, j, 1) <> "0" Then
      '↑如果T變數從j變數開始取1個字的字元不是"0"
         Z = Mid(T, j + 1)
         '↑令Z這字串變數是 T變數從j+1個字開始到 最後字之間的字串
         Exit For
         '↑跳出j迴圈
      End If
   Next
   T = Replace(UCase(Trim(Brr(i, 1))), "0", "") & Z
   '↑令T變數是 i迴圈列第1欄Brr陣列值 去除前後空白字元,經轉化英文字母為大寫,
   '再將0置換為空字元,最後連接Z變數 組成新的字串變數
   If Y.Exists(T) = Empty Then
   '↑如果以T變數查Y字典裡沒有這key?
      Y(T) = Brr(i, 2)
      '↑令以T變數當key,Item是i迴圈列第2欄Brr陣列值
      ElseIf Y(T) <> Brr(i, 2) Then
      '↑否則如果以T變數查Y字典所回傳值不同於 i迴圈列第2欄Brr陣列值??
         MsgBox Brr(i, 1) & " 去0簡化後的資料欄有同編號不同商品疑慮"
         '↑跳出提示窗~~
         Exit Sub
         '↑結束程式執行
   End If
i01:
Next
For i = 1 To UBound(Crr)
'↑設順迴圈!i從1到 Crr陣列縱向最大索引列號
   T = Trim(Crr(i, 1)): If T = "" Then GoTo i02
   '↑令T變數是 i迴圈列第1欄陣列值去除前後空白字元後的新字串,
   '如果T變數是空字元!就跳到 i02位置繼續執行
   V = Len(T)
   '↑令V這短整數變數是 T變數的字元數
   For j = V To 1 Step -1
   '↑設順迴圈!j從V變數到 1,令每個迴圈j都要-1
      If Mid(T, j, 1) <> "0" Then
      '↑如果T變數從j變數開始取1個字的字元不是"0"
         Z = Mid(T, j + 1)
         '↑令Z這字串變數是 T變數從j+1個字開始到 最後字之間的字串
         Exit For
         '↑跳出j迴圈
      End If
   Next
   T = Replace(UCase(Trim(Crr(i, 1))), "0", "") & Z
   '↑令T變數是 i迴圈列第1欄Brr陣列值 去除前後空白字元,經轉化英文字母為大寫,
   '再將0置換為空字元,最後連接Z變數 組成新的字串變數
   Crr(i, 1) = Y(T)
   '↑令i迴圈列第1欄Crr陣列值是 以T變數查Y字典所回傳的Item值
i02:
Next
[I2].Resize(UBound(Crr), 1) = Crr
'↑令[I2]擴展向下Crr陣列縱向最大索引列號數 的儲存格範圍,以Crr陣列值帶入
Erase Brr, Brr: Set Y = Nothing
'↑釋放變數
End Sub
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

        靜思自在 : 人生不一定球球是好球，但是有歷練的強打者，隨時都可以揮棒。
123
返回列表 上一主題