Eliminate wild characters

[MrRJ]

Hi,
Is there a way 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.

 

[Luke M]

Select the range/column you want to deal with.

Open Find&Replace (Ctrl+H)

Use a tilde in front of wildcard (e.g., ~* finds the * symbol)
In replace, input a single space " "
Under options, make sure you are looking in sheet, not workbook.
Replace all.

Repeat as needed for other various wildcard symbols.

 

[Mike H]

Hi,

And in this context what is your definition of 'wild characters'?

Mike

 

[MrRJ]

Luke,
Thanks for your quick reply. I wasn't clearer before, I am looking for a
VBA code to do this. Also, I want to replace all wild characters, like these
~!@#$%^&*()

Any ideas?

Select the range/column you want to deal with.

Open Find&Replace (Ctrl+H)

Use a tilde in front of wildcard (e.g., ~* finds the * symbol)
In replace, input a single space " "
Under options, make sure you are looking in sheet, not workbook.
Replace all.

Repeat as needed for other various wildcard symbols.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi,
Is there a way 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.

 

[MrRJ]

Mike,
Sorry if I was not clear before.
I am looking for a VBA code to replace wild characters ~!@#$%^&*() with a
space. Does that help?

Hi,

And in this context what is your definition of 'wild characters'?

Mike

Hi,
Is there a way 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.

 

[Ron Rosenfeld]

Hi,
Is there a way 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