VBA实用小程序65： 实现多重替换

学习Excel技术，关注微信公众号：

excelperfect

这是在dailydoseofexcel.com中学到的技术，使用一个自定义函数来实现冗长的多重替换。

例如，想要将单元格A2中的字符串：

qaINCf/a,AND'&uv-LLCg.h/DBAuiLTD

中的“AND”、“INC”、“LLC”、“LTD”、“DBA”、空格、句点、逗号、连接符、斜杠、单引号替换掉。

一般会想到使用SUBSTITUTE函数，很自然的编写公式为：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"AND",""),"INC",""),"LLC",""),"LTD",""),"DBA",""),"",""),".",""),",",""),"&",""),"-",""),"/",""),"'","")

得到结果：

QAFAUVGHUI

虽然获得了结果，但公式非常冗长，如果要替换的字符越多，则嵌套越多。此时，可以使用VBA编写一个自定义函数来解决。

代码如下：

代码语言：javascript复制

Public Function Subst( _
  text As String, _
  NewText As String, _
  ParamArray OldText() As Variant) _
  As String
    Dim vItem As Variant
    Dim sReturn As String
    Dim vArray As Variant
   
    sReturn = text
   
    vArray = OldText
    BubbleSortLen vArray
   
    For Each vItem In vArray
        sReturn = Replace(sReturn, vItem,NewText, , , vbTextCompare)
    Next vItem
   
    Subst = sReturn
   
End Function
 
Public Sub BubbleSortLen(ByRef vArray As Variant)
    Dim i As Long
    Dim j As Long
    Dim sTemp As String
   
    For i = LBound(vArray) To UBound(vArray) -1
        For j = i To UBound(vArray)
            If Len(vArray(j)) >Len(vArray(i)) Then
                sTemp = vArray(i)
                vArray(i) = vArray(j)
                vArray(j) = sTemp
            End If
        Next j
    Next i
End Sub

此时，在单元格中输入公式：

=Subst(UPPER(A2),"","AND","INC","LLC","LTD","DBA","CO","",".",",","&","-","/","'")

将得到相同的结果。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。

vba com dba excel

0 人点赞