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.

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!*


"MrRJ" wrote:

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.

Hi,

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

Mike

"MrRJ" wrote:

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,
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?

"Luke M" wrote:

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!*


"MrRJ" wrote:

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.

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?



"Mike H" wrote:

Hi,

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

Mike

"MrRJ" wrote:

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.

On Wed, 15 Apr 2009 12:08:14 -0700, MrRJ
wrote:

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