Replacing asterix characters in Excel

Replacing asterix characters in Excel

One of the unusual features of Excel is that it is not easy if you are replacing asterix characters in Excel. The same applies to question marks. In fact if you try and replace an asterix (*) or a question mark (?) you get some strange results and could corrupt your whole document.

The reason for this is that the asterix and question mark are used as wildcard characters and mean something else when you are doing a search (and replace). An asterix tells Excel to look for any number and┬átype of characters. So if a cell contains the word ‘test*’, when you try replace the * with something else, the entire cell is replaced with the new characters. This is because to Excel the * means the whole cell and it follows those rules.

Similarly with a question mark (?), Excel thinks you are looking for a single character (each ? equates to a single character) but it doesn’t care what that character is. In this case each character in the word ‘test*’ will be replaced with the replacement characters.

How to replace an asterix or question mark in Excel

The solution is to include a tilde (~) in front of the * or ?. The tilde key is normally above your TAB key and you need to hold SHIFT down.

So, in your search/ replace screen you would type

~*

to tell Excel to actually search for the asterix and not treat it as a wildcard character.

~? would be used when trying to replace actual question marks in the text.