Substitute Formula for 18 Characters?

[Southerncentralrain]

I am trying to write a formula to replace common non-ASCII characters that are produced in word with the ASCII equivalent. Below is the list of non-ASCII characters:

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">£</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">©</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">®</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">²</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">¼</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">½</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">¾</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">í</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">–</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">—</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">’</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">“</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">”</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">•</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">…</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">…</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">℠</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">™</td> </tr> </tbody></table>
I am looking to replace them with the following ASCII characters:

<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">gbp</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">(c)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">(r)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">^2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1/4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1/2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3/4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">i</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">-</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">'</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">"</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">"</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">*</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">...</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">...</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">(sm)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">(tm)</td> </tr> </tbody></table>
Is this possible to do? Thanks in advance for any help with this.

 

[Robert Mika]

You can write a quite long formula using nested SUBSTITUTE function.

 

[Southerncentralrain]

Thanks for your response but I believe it has a limit of 8. I actually just recorded a Macro and it seems to be working very good for this. Thanks again for your time.

 

[Rick Rothstein]

I actually just recorded a Macro and it seems to be working very good for this.

You originally asked for a formula, so I assumed you wanted to preserve your original text but be able to work with it by replacing the characters via a formula for use in different cells. If that is the case, here is a UDF you can use...

Function FixWordChars(S As String) As String
  Dim X As Long, CharPostion As Long, FindMe As String, ReplaceWith As String, Replacements() As String
  Const CharactersToBeReplaced As String = "£©®²¼½¾í–—’“”•……?™"
  Const ReplacementText As String = "gbp,(c),(r),^2,1/4,1/2,3/4,i,-,-,',"","",*,...,...,(sm),(tm)"  'Note: No spaces around commas
  Replacements = Split(ReplacementText, ",")
  FixWordChars = S
  For X = 1 To Len(CharactersToBeReplaced)
    FindMe = Mid(CharactersToBeReplaced, X, 1)
    CharPostion = InStr(CharactersToBeReplaced, FindMe)
    ReplaceWith = Replacements(CharPostion - 1)
    FixWordChars = Replace(FixWordChars, FindMe, ReplaceWith)
  Next
End Function

I know you said you have a working macro, but the above can be recast into a macro quite easily, so I figured I would show you it.

Sub FixWordChars()
  Dim X As Long, CharPostion As Long, FindMe As String, ReplaceWith As String, Cell As Range, Replacements() As String
  Const CharactersToBeReplaced As String = "£©®²¼½¾í–—’“”•……?™"
  Const ReplacementText As String = "gbp,(c),(r),^2,1/4,1/2,3/4,i,-,-,',"","",*,...,...,(sm),(tm)"  'Note: No spaces around commas
  Replacements = Split(ReplacementText, ",")
  For Each Cell In Selection
    For X = 1 To Len(CharactersToBeReplaced)
      FindMe = Mid(CharactersToBeReplaced, X, 1)
      CharPostion = InStr(CharactersToBeReplaced, FindMe)
      ReplaceWith = Replacements(CharPostion - 1)
      Cell.Value = Replace(Cell.Value, FindMe, ReplaceWith)
    Next
  Next
End Sub

Note that this macro works on the selection of cells, so select your cells before running it... the selected cells do not all have to have characters to be replaced, but the less cells you make the macro process, the faster it will complete its task.

 

[Southerncentralrain]

Wow. Thank you very much for that. Very impressive.