Normally, the excel substitute() function is limited to finding 1 string and substituting said string for another string. This Excel User Defined Function allows you to substitute multiple sets of strings/characters. I created a sheet with 2 columns (as named ranges), “CleanOLD” and “CleanNew” and used the following formula =SubstituteMultiple(TRIM(CLEAN(A1)),CleanOld,CleanNew)

I CLEAN first to remove any non-printable characters and then TRIM to remove any extra spaces. I’ve mainly used this to prepare strings for comparison but its uses are limited only by your imagination.

'UDF to substitute multiple characters, from http://www.get-digital-help.com/2013/02/27/substitute-multiple-text-strings-vba/
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)
Dim i As Single
For i = 1 To old_text.Cells.Count
Result = Replace(LCase(text), LCase(old_text.Cells(i)), LCase(new_text.Cells(i)))
text = Result
Next i
SubstituteMultiple = Result
End Function