ReplaceMissingCharacters
This function returns the pattern in the second parameter, where the characters not present in the range specified in the first parameter are replaced with the third parameter.
1Function ReplaceMissingCharacters(range As Range, pattern As String, replacement As String) As String
2 Dim car As String
3 Dim i As Integer
4 Dim foundChar As Boolean
5
6 For i = 1 To Len(pattern)
7 car = Mid(pattern, i, 1)
8 foundChar = False
9
10 ' Check is present
11 For Each cell In range
12 If InStr(1, cell.Value, car) > 0 Then
13 foundChar = True
14 Exit For
15 End If
16 Next cell
17
18 ' Char not found
19 If Not foundChar And car <> " " Then
20 Mid(pattern, i, 1) = replacement
21 End If
22 Next i
23
24 ReplaceMissingCharacters = pattern
25End Function