Excel嵌套的替代函数宏? (超过64个巢) [英] Excel nested Substitute function macro ? (more than 64 nest)

问题描述

我想在Excel中创建一个宏，以将工作表中每个单词的字符替换为同一单元格中新的其他工作表中的某些不同字符.我已经使用了Substitude功能，但是它只允许用于64级.我有大约100个或更多的巢.请指导....

Hi I want to create a macro in Excel to replace characters of each word in a sheet to some different characters in new other sheet in the same cell. I have used substitude funtion but it allows me to use it for 64levels only. I have about 100 or more nests. Please guide....

例如:

=
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
G1,
"a","T"),
"b","p"),
"c","u"),
"d","d"),
"e","J"),
"f","v"),
"g","r"),
"h","j"),
"i","f"),
"j","i"),
"k","e"),
"l","b"),
"m","w"),
"n","B"),
"o","'"),
"p","g"),
"q","s"),
"r","o"),
"s",";"),
"t","N"),
"u","["),
"v","t"),
"w","k"),
"x","D"),
"y","/"),
"z","I"),
"0","0"),
"1","1"),
"2","2"),
"3","3"),
"4","4"),
"5","5"),
"6","6"),
"7","7"),
"8","8'"),
"9","9"),
"10","10"),
"A","n"),
"B","G"),
"C","S"),
"D","X"),
"E","U"),
"F","Y"),
"G","x"),
"H","Q"),
"I","h"),
"J","M"),
"K","y"),
"L","+"),
"M","z"),
"N","A"),
"O","""),
"P","c"),
"Q","E"),
"R","q"),
"S","P"),
"T","m"),
"U","{"),
"V","V"),
"W","K"),
"X",":"),
"Y","""),
"Z","}"),
"0","0"),
"%","#"),
"^","\"),
"&","|"),
"*","!"),
"(","("),
")",")"),
"=","&"),
"+","O'"),
"[","."),
"]","]")

推荐答案

您可以在模块中添加以下函数，然后在公式中使用它:

You could add the following function in a module and then use it in a formula:

Function ReplaceSpecial(ByVal theString As String, ByVal find As String, ByVal replacement As String) As String
    Dim i As Integer, pos As Integer
    For i = 1 To Len(theString)
        pos = InStr(find, Mid(theString, i, 1))
        If pos > 0 Then Mid(theString, i, 1) = Mid(replacement, pos, 1)
    Next
    ReplaceSpecial = theString
End Function

用法:

您可以像公式一样使用它.像这样

You can use it like a formula. Like this,

=ReplaceSpecial(G1, "abcdefghijklmnopqrstuvwxyz012345678910ABCDEFGHIJKLMNOPQRSTUVWXYZ0%^&*()=+[]", "TpudJvrjfiebwB'gso;N[tkD/I01234567890nGSXUYxQhMy+zA""cEqPm{VK:}0#\|!()&O.]")

或者，您可以像宏一样使用它.实现方式取决于您的查找和替换值所在的位置.假设它们分别在A列和B列中，则可以添加以下宏并使用它.

Or, you can use it like a macro. Implementation depends on where your find and replacement values are. Assuming they are in columns A and B respectively, you can add the following macro and use it.

Sub ReplaceSpecialMacro()
    Dim find As String, replacement As String, result As String
    find = Join(Application.Transpose(Range("A:A").Value), "")
    replacement = Join(Application.Transpose(Range("B:B").Value), "")
    result = ReplaceSpecial(ActiveCell, find, replacement)
    MsgBox result           '-- this is just for demo. you may put it in a cell etc.
End Sub

以下宏将在所有/选定的单元格上运行ReplaceSpecial:

The following macro will run ReplaceSpecial on the all/selected cells:

Sub ReplaceSpecialMacro()
    Dim find As String, replacement As String, currentCell As Excel.Range
    find = "abcdefghijklmnopqrstuvwxyz012345678910ABCDEFGHIJKLMNOPQRSTUVWXYZ0%^&*()=+[]"
    replacement = "TpudJvrjfiebwB'gso;N[tkD/I01234567890nGSXUYxQhMy+zA""cEqPm{VK:}0#\|!()&O.]"
    Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select  '-- comment out this line if you want to run only on currently selected cells
    For Each currentCell In Selection
        currentCell = ReplaceSpecial(currentCell, find, replacement)
    Next
    MsgBox "Done!"
End Sub

HTH.

这篇关于Excel嵌套的替代函数宏? (超过64个巢)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！