90. Excel Tips - Replace The * Character

Nick's picture


The * character needs special attention if you want to replace it in your spreadsheet

  • Replacing * with something will replace everything (not just the * character)

Here's a screen shot of our data in Excel:

replace-the-*-character

And let's say we want to replace * with "BBB":

replace-the-*-character

Here's how to do it:

  1. Press CTRL + H
  2. Type ~* in the Find What section
  3. type BBB in the Replace With
  4. Press Replace All

NOTE: The "~" character tells Excel that you only want to replace the * character, not everything.

Download sheet to practise how to Replace The * Character in Excel

Training Video on how to Replace The * Character in Excel:

AttachmentSize
replace-the-star-character.xls35 KB
Nick's picture

Also note that you need to

Also note that you need to use "~~" to replace the "~" character, and "~?" to replace "?"

NimaIranian's picture

replace * by formulas

could we replace * sign via Replace or substitute function and how?