CODEWITHSUNDEEP
excelvba
jatin verma
jatin verma

Reputation: 177

Excel vba to remove "?" (question mark) and "*" (star)

I am trying to replace all special characters in an Excel sheet.

But ? (question mark) and * (asterisk) are resulting in a blank cell

The code I'm using:

Selection.Replace What:="!", Replacement:=" "
Selection.Replace What:="@", Replacement:=" "
Selection.Replace What:="#", Replacement:=" "

.... so on

Upvotes: 4

Views: 8504

Answers (2)

Jordan
Jordan

Reputation: 4514

Question marks and asterisks are known as wildcards in find and replace functions, this means they already mean something other than their string values. In order to get round this you need to precede them with a tilde (~).

Try:

Selection.Replace What:="~?", Replacement:=" "
Selection.Replace What:="~*", Replacement:=" "

Here's a helpful link: https://support.microsoft.com/en-gb/kb/214138

Upvotes: 8

Amit Kohli
Amit Kohli

Reputation: 2950

Use regular expressions: http://analystcave.com/excel-regex-tutorial/

It's a much more versatile way to do text-matching anyway.

Upvotes: 0

Related Questions