【已结贴】批量替换字符

象山海鲜

多了6个字符

1. =RIGHT(SUM(LOOKUP(ROW(2:10),ROW(1:10)*{1,0}-LEFT(RIGHT(1&B2&0,ROW($1:10)))%)*10^ROW($2:$10)),LEN(B2))

复制代码

袁勇

1. Private Sub CommandButton1_Click()
   
2. maxr = Range("a65536").End(xlUp).Row
   
3. Range("d2:d" & maxr).ClearComments
   
4. For i = 2 To maxr
   
5.     s = Cells(i, 2) & "0"
   
6.     L = Len(s)
   
7.    
   
8.     For j = L - 1 To 1 Step -1
   
9.         If IsNumeric(Mid(s, j, 1)) * 1 = 0 Then
   
10.            s = Application.WorksheetFunction.Replace(s, j, 1, Mid(s, j + 1, 1))
    
11.         End If
    
12.     Next j
    
13.     Cells(i, 4) = Left(s, L - 1)
    
14. Next i
    
15. End Sub

复制代码

丢丢表格

象山海鲜 发表于 2021-3-15 14:10
多了6个字符

要是没有空值的话。。。。。

=SUM(-LOOKUP(A$3:A$11,A$2:A$11*{1,0}-LEFT(RIGHT(0&B2&0,A$2:A$11))%)*10^A$3:A$11)

@刘卓

优秀，强大，留个记号