Remove Special Characters 3

[V00D00]

Hello,

I am looking for a way to remove special characters from a sheet. These special characters being the '*' and '?'. Using the normal code for find/replace does not have the effect I am looking for, it removes and replaces all characters.

Any help would be appreciated.

 

[firefytr]

Check out ASAP Utilities and it's advanced character removal. Download from

http://www.asap-utilities.com

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[V00D00]

I was hoping for something in VBA. Is it possible to use the unicode representation of a character for replace code? If so, do you have a example so I can learn the formating?

 

[AnotherHiggins]

You could use something like this:

Sub EliminateAsterisks()
For Each cell In Selection
    cell.Value = Application.WorksheetFunction.Substitute(cell, "*", "")
Next cell
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

No unicode needed. But let me reiterate, GET ASAP UTILITIES. If you want to do stuff like this in about 4 seconds or less, you'll make the download.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[V00D00]

Thank you for your help. Although the ASAP utilities perform this function, it requires human intelligence to operate. I am looking for a code only option.

I did a little more research and was able to do a regular find and replace of the '*' character by just preceding it with a '~'. So the code looks like:

Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False