I did this as an exercise. I'm not convinced that my approach is particularly good and it could potentially take some time to process but here goes...
I set up a couple of arrays. One holds the symbol that could be affected and the other holds the rules as laid out in your question. The indexes of each element of each array must match up.
Dim arrSymbols(19) As String
Dim arrCorrect(19) As String
Public Sub SetUp()
arrSymbols(1) = ".": arrCorrect(1) = ". "
arrSymbols(2) = ":": arrCorrect(2) = ": "
arrSymbols(3) = ",": arrCorrect(3) = ", "
arrSymbols(4) = "(": arrCorrect(4) = " ("
arrSymbols(5) = ")": arrCorrect(5) = ") "
arrSymbols(6) = "/": arrCorrect(6) = "/"
arrSymbols(7) = "-": arrCorrect(7) = " - "
arrSymbols(8) = "!": arrCorrect(8) = "!"
arrSymbols(9) = "#": arrCorrect(9) = "#"
arrSymbols(10) = "*": arrCorrect(10) = "*"
arrSymbols(11) = ";": arrCorrect(11) = "; "
arrSymbols(12) = "_": arrCorrect(12) = "_"
arrSymbols(13) = "{": arrCorrect(13) = " {"
arrSymbols(14) = "}": arrCorrect(14) = "} "
arrSymbols(15) = "'": arrCorrect(15) = "'"
arrSymbols(16) = Chr$(145): arrCorrect(16) = Chr$(145)
arrSymbols(17) = Chr$(146): arrCorrect(17) = Chr$(146)
arrSymbols(18) = Chr$(147): arrCorrect(18) = " " & Chr$(147)
arrSymbols(19) = Chr$(148): arrCorrect(19) = " " & Chr$(148)
End Sub
I included a function to see if any of the symbols actually appear anywhere in the input string. To try and improve performance it returns immediately it finds any of the symbols...
Public Function Contains(Test As String, Against() As String) As Boolean
Dim i As Integer
Contains = False
For i = 1 To UBound(Against)
If InStr(Test, Against(i)) Then
Contains = True
Exit For
End If
Next
End Function
The actual function that does the work first checks to see if anything needs to be done (and if not, just returns the original string input).
For each of the symbols to be considered, and only if that symbol appears in the input string, it replaces all instances of a space + symbol with just symbol and also replaces all instances of symbol + space with just the symbol.
Finally, it replaces the symbol with the corresponding content of the
arrCorrect
, only if there are spaces in the rules though.
Public Function AdjustSymbols(ByVal sInput As String) As String
If Not Contains(sInput, arrSymbols) Then
AdjustSymbols = sInput
Exit Function
End If
Dim sOut As String
sOut = sInput
Dim i As Integer
For i = 1 To UBound(arrSymbols)
If InStr(sOut, arrSymbols(i)) Then
While InStr(sOut, " " + arrSymbols(i)) > 0
sOut = Replace(sOut, " " + arrSymbols(i), arrSymbols(i))
Wend
While InStr(sOut, arrSymbols(i) + " ") > 0
sOut = Replace(sOut, arrSymbols(i) + " ", arrSymbols(i))
Wend
If Not arrSymbols(i) = arrCorrect(i) Then
sOut = Replace(sOut, arrSymbols(i), arrCorrect(i))
End If
End If
Next
AdjustSymbols = sOut
End Function
And an example from my testing
Call SetUp
Dim aStringTest As String
aStringText = "i**! _ (,."
Debug.Print "!" + AdjustSymbols(aStringText) + "!"
You would pass each cell that you want to consider into this function.