Eliminate wild characters

[MrRJ]

Hi,
Is there a VBA code to eliminate wild characters ~!@#$%^&*() and replacing
them with a space?
I found this on this site and looks like it could work but need to
incoporate the wild characters and in a specific column.

Function RemAlpha(str As String) As String
With CreateObject("VbScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "[A-Z]"
RemAlpha = .Replace(str, vbNullString)
End With
End Function

I appreciate any help you can give.

 

[Rick Rothstein]

Give this function a try...

Function WildcardsToSpaces(ByVal S As String) As String
Dim X As Long
Const Wildcards As String = "~!@#$%^&*()"
For X = 1 To Len(Wildcards)
S = Replace(S, Mid(Wildcards, X, 1), " ")
Next
WildcardsToSpaces = S
End Function

 

[Peter T]

Function WildStripper(stext) As String
Dim i As Long, j As Long
Dim ba1() As Byte, ba2() As Byte
Const cSTRIP As String = "~!@#$%^&*()"

ba1 = stext
ba2 = cSTRIP
For i = 0 To UBound(ba1) - 1 Step 2
For j = 0 To UBound(ba2)
If ba1(i) = ba2(j) Then
If ba1(i + 1) = ba2(j + 1) Then
ba1(i) = 32
ba1(i + 1) = 0
End If
End If
Next
Next
WildStripper = ba1
End Function

If you have many more characters to remove the above would probably be
faster adapted to use Select case.

This approach might not be suitable with 'wide' characters.

Regards,
Peter T

 

[Peter T]

On second thoughts, go with Rick's Replace for this

Regards,
Peter T

Function WildStripper(stext) As String
Dim i As Long, j As Long
Dim ba1() As Byte, ba2() As Byte
Const cSTRIP As String = "~!@#$%^&*()"

ba1 = stext
ba2 = cSTRIP
For i = 0 To UBound(ba1) - 1 Step 2
For j = 0 To UBound(ba2)
If ba1(i) = ba2(j) Then
If ba1(i + 1) = ba2(j + 1) Then
ba1(i) = 32
ba1(i + 1) = 0
End If
End If
Next
Next
WildStripper = ba1
End Function

If you have many more characters to remove the above would probably be
faster adapted to use Select case.

This approach might not be suitable with 'wide' characters.

Regards,
Peter T

Hi,
Is there a VBA code to eliminate wild characters ~!@#$%^&*() and
replacing
them with a space?
I found this on this site and looks like it could work but need to
incoporate the wild characters and in a specific column.

Function RemAlpha(str As String) As String
With CreateObject("VbScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "[A-Z]"
RemAlpha = .Replace(str, vbNullString)
End With
End Function

I appreciate any help you can give.

 

[meh2030]

Hi,
Is there a VBA code to eliminate wild characters ~!@#$%^&*() and replacing
them with a space?
I found this on this site and looks like it could work but need to
incoporate the wild characters and in a specific column.

Function RemAlpha(str As String) As String
  With CreateObject("VbScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "[A-Z]"
    RemAlpha = .Replace(str, vbNullString)
  End With
End Function

I appreciate any help you can give.

Mr. RJ,

Excel has a CHAR function (Chr() in VBA). This will return a
corresponding character given a numeric input. CHAR will return
values for the numbers 1 to 255. In other words, the following
wildcards you listed have the corresponding character numbers in
Excel: ~!@#$%^&*(); 126, 33, 64, 35, 36, 37, 94, 38, 42, 40, 41. (As
a side note, if you create a spreadsheet with the CHAR function, then
searching for ~, *, or ? requires a ~ prefix for the find method. For
example, if you want to force Excel to find the ~, then Ctrl + f, Find
What: ~~). Code to do your replacement is below.

[It might be worth it to look into the SUBSTITUTE formula. I've never
had occassion to use SUBSTITUTE nor have I ever used array constants
in a formula before, but I'm sure there is something out there for a
formula that will do the trick for you. Looks like I need to read up
on it myself. I tried the following: =SUBSTITUTE(B17,
{"~","!","@","#","$","%","^","&","*","(",")"},"") but didn't receive
the desired outcome.]

Best,

Matthew Herbert

Function ReplaceCharacters(Str As String) As String

Dim varArrRep As Variant
Dim strReplaced As String
Dim intI As Integer

'array that contains your desired string replacement
' CHAR function in Excel used to determine values
' CHAR valid for 1 - 255
varArrRep = Array(126, 33, 64, 35, 36, 37, 94, 38, 42, 40, 41)

'store the argument into another variable
strReplaced = Str

'loop through each array item and replace any characters in
' Str that match the varArrRep characters with a space (i.e. Chr(13))
For intI = LBound(varArrRep) To UBound(varArrRep)
strReplaced = Replace(strReplaced, Chr(varArrRep(intI)), Chr(13))
Next

'return the result to the function
ReplaceCharacters = strReplaced

End Function

 

[Ron Rosenfeld]

Hi,
Is there a VBA code to eliminate wild characters ~!@#$%^&*() and replacing
them with a space?
I found this on this site and looks like it could work but need to
incoporate the wild characters and in a specific column.

Function RemAlpha(str As String) As String
With CreateObject("VbScript.RegExp")
.Global = True
.IgnoreCase = True
.Pattern = "[A-Z]"
RemAlpha = .Replace(str, vbNullString)
End With
End Function

I appreciate any help you can give.

Something like this:

==================================
Option Explicit
Sub KillWild()
Dim rng As Range, c As Range
Dim re As Object

Set rng = Selection 'or whatever
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[~!@#$%^&*]"

For Each c In rng
c.Value = re.Replace(c.Value, " ")
Next c
End Sub
==================================

will remove the characters in your list and replace each one with a <space>.

In the above, rng is set to "Selection". But you could just as easily set it
to a specified range.

Also, as written, the function will replace *each* wild character with a space;
so if you have several in a row, there will be several spaces; or if there is a
space followed by a wild character, there will be several spaces.

If you want to only be left with a single space in those instances, make this
small change:


re.Pattern = "[\s~!@#$%^&*]+"
--ron