Excell : Multiple Substitute

Sometime, when you are working on formula, you need to substitute many different characters, and your formula become very-very long.


To solve it, please use VBA code below, so, you will have a single instruction only. 


Function MultiSubstitute(CellToChange As Range, _
NameNumber As String) As String

  Dim X As Long
  Dim Data() As String
  Data = Split(NameNumber, ",")

  If (UBound(Data) + 1) Mod 2 Then
      MultiSubstitute = "#MISMATCH!"
      Exit Function
  Else
      MultiSubstitute = CellToChange.Value
      For X = 0 To UBound(Data) Step 2
      MultiSubstitute = Replace(MultiSubstitute, Data(X), _
      Data(X + 1), , , vbTextCompare)
      Next
   End If
End Function


After finish, you only need to type the following in to "cell", let say if you wan to change cell "C3"


=MultiSubstitute(C3;"é,e,à,a,ç,c,è,e")


P.S. The mismatch in the function is used to make sure you always have couples of data, in this case an accented and non-accented character
Under permission of : 2013.net