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

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编写一个自定义函数来解决。

代码如下：

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 iEnd Sub

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

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

将得到相同的结果。