VBA code to remove characters

[zaska]

Hello,

Can anyone help me with a vba code to remove characters from selection

1. Remove Numeric characters
2. Remove Alphabetic characters
3. Remove Non-Numeric characters
4. Remove Non Alphabetic characters
5. Remove Non Alpha-Numeric characters
6. Remove Unprintable characters

Thanks

Zaska

 

[swastik]

Sir, the macro is getting stopped on Replace on the line Arr(X, Y) = Replace("0", "") and showing following error message

Compile error:
Argument not optional

Can you please help in resolving this error.

Regards

Swastik

Use this structure for each case, just change the line highlighted code lines in red and blue to the indicated code statements shown in the write-up following the code...

Sub ReplaceThings()
  Dim X As Long, Y As Long, Z As Long, Arr As Variant
  Arr = Selection.Value
  For X = LBound(Arr) To UBound(Arr)
    For Y = LBound(Arr, 2) To UBound(Arr, 2)
      For Z = 1 To Len(Arr(X, Y))
        [COLOR=red][B]<<< see write-up >>>[/B][/COLOR]
[COLOR=black]   Next[/COLOR]
      [B][COLOR=blue]<<< see write-up >>>[/COLOR][/B]
    Next
  Next
  Selection = Arr
End Sub

Use these code lines in place of the ones highlighted in red and blue...

1. Remove Numeric characters
[COLOR=red][B]Red:[/B][/COLOR] If Mid(Arr(X, Y), Z, 1) Like "#" Then Arr(X, Y) = Mid(Arr(X, Y), Z) = "0"
[B][COLOR=blue]Blue:[/COLOR][/B] Arr(X, Y) = Replace("0", "")
 
2. Remove Alphabetic characters
[B][COLOR=red]Red:[/COLOR][/B] If Mid(Arr(X, Y), Z, 1) Like "[A-Za-z]" Then Arr(X, Y) = Mid(Arr(X, Y), Z) = "A"
[B][COLOR=blue]Blue:[/COLOR][/B] Arr(X, Y) = Replace("A", "")
 
3. Remove Non-Numeric characters
[COLOR=red][B]Red:[/B][/COLOR] If Mid(Arr(X, Y), Z, 1) Like "[!0-9]" Then Arr(X, Y) = Mid(Arr(X, Y), Z) = "A"
[COLOR=blue][B]Blue:[/B][/COLOR] Arr(X, Y) = Replace("A", "")
 
4. Remove Non Alphabetic characters
[B][COLOR=red]Red:[/COLOR][/B] If Mid(Arr(X, Y), Z, 1) Like "[!A-Za-z]" Then Arr(X, Y) = Mid(Arr(X, Y), Z) = "0"
[B][COLOR=blue]Blue:[/COLOR][/B] Arr(X, Y) = Replace("0", "")
 
5. Remove Non Alpha-Numeric characters
[COLOR=red][B]Red:[/B][/COLOR] If Mid(Arr(X, Y), Z, 1) Like "[!A-Za-z0-9]" Then Arr(X, Y) = Mid(Arr(X, Y), Z) = "@"
[B][COLOR=blue]Blue:[/COLOR][/B] Arr(X, Y) = Replace("@", "")
 
6. Remove Unprintable characters
[COLOR=red][B]Red:[/B][/COLOR] If Asc(Arr(X, Y)) < 32 Then Arr(X, Y) = Mid(Arr(X, Y), Z) = vbTab
[B][COLOR=blue]Blue:[/COLOR][/B] Arr(X, Y) = Replace(vbTab, "")

I did not actually test these, but they should work for you without any problems.