Excel nested Substitute function macro ? (more than 64 nest)

Question

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....

For Example:

=
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'"),
"[","."),
"]","]")

Answer 1

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

Usage:

You can use it like a formula. Like this,

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

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

EDIT :

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.

Answer 2

Here is a very simple example that does 75 substitutions:

Public Function scramble(SIN As String) As String
    Dim temp As String, L As Long, i As Long
    Dim CH As String

    s1 = "0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz"
    s2 = "FPmbaXO`qwJz^_v:EY7yVehU6TDjBN45k]oplxMS8HA;[\u0ZfCri2>I9?n@=ts1QG3gd<LRcWK"
    L = Len(SIN)
    scramble = ""
    temp = ""

    For i = 1 To L
        CH = Mid(SIN, i, 1)
        j = InStr(s1, CH)
        If j = 0 Then
            temp = temp & CH
        Else
            temp = temp & Mid(s2, j, 1)
        End If
    Next i

    scramble = temp
End Function

The original characters are in the variable s1 and the substituted characters are in the variable s2. For example: