Excel reverse REPLACE

Question

I am working on some software that cleans up data before sending it into another system. The data comes from all around the world and contains a variety of characters that have to be replaced. For example ‘, : ; @

The system that accepts the parsed data has very strict character set. It allows the letters A to Z (upper case only) the numerals 0 to 9 the special characters / -. Space < =

The data arrives in Excel spreadsheets so I have written the following code in a visual basic macro.

fhl_str contains the data to be cleansed

fhl_str = Replace(fhl_str, ",", " ")
fhl_str = Replace(fhl_str, "'", " ")
fhl_str = Replace(fhl_str, ":", " ")
fhl_str = Replace(fhl_str, ";", " ")
fhl_str = ucase(fhl_str)

Now, each time a new unwanted character arrives we have to add a new line of code. e.g. fhl_str = Replace(fhl_str, "@", " ")

My question is Could I reverse the logic so that the macro looks for A to Z and 0 to 9 and deletes anything else. That way my code would be future proof for new unwanted characters.

Thanks

You could with a [regular expression](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835) — cybernetic.nomad, Jun 12 '18 at 17:03

braX · Answer 1 · 2018-06-12T19:00:34.163

Here's some VBA that will do it if you find regex difficult to understand. It uses the ASCII code to determine the only characters to allow. If your scope changes you can modify the ASCII numbers in the Case statement.

Public Function RemoveSpecial(s As String) As String
  Dim sResult As String
  Dim nIndex As Integer

  s = UCase$(s)
  For nIndex = 1 To Len(s)
    Select Case Asc(Mid$(s, nIndex, 1))
      Case 65 To 90, 45 To 57, 32, 60 To 61
        sResult = sResult & Mid$(s, nIndex, 1)
      Case Else
        sResult = sResult & " "
    End Select
  Next
  RemoveSpecial = sResult

End Function

Usage:

Debug.Print RemoveSpecial("TeSt<>=.@@")

or something like:

Range("A1") = RemoveSpecial("TeSt<>=.@@")

ASCII Codes

score 1 · Answer 2 · answered Jun 12 '18 at 17:12

If you want to replace bad characters with a single space:

Sub KeepOnlyTheGood()
    Dim i As Long, L As Long, v As String, CH As String
    Dim r As Range

    For Each r In Selection
        t = ""
        v = r.Value
        L = Len(v)
        For i = 1 To L
            CH = Mid(v, i, 1)
            If CH Like "[0-9A-Z]" Or CH = "/" Or CH = "-" Or CH = "." Or CH = " " Or CH = "<" Or CH = "=" Then
                t = t & CH
            Else
                t = t & " "
            End If
        Next i
        r.Value = t
    Next r
End Sub

you can condense down the long line to `If CH Like "[0-9A-Z<=.-/]" Then`. Example as an immediate window function `s=[A1]:t="":For i=1To Len(s):c=mid(s,i,1):t=t &IIf(c Like"[0-9A-Z<=.-/]",c," "):next:?t` — Taylor Alex Raine, Jun 12 '18 at 19:11

Excel reverse REPLACE

2 Answers2