How to remove the character "*" in Excel 2010?

Question

I have regressions tables in an Excel spreadsheet where the significativity is indicated with stars (*) characters. Those are attached to the number in the cell and the number of stars is not constant: sometimes three, sometimes two, sometimes one and sometimes none.

I would like to remove those asterisks such that I can use the number in the cell (it is currently understood as text by Excel because of the presence of the asterisk.)

I tried the following:

• search and replace the character * , but since Excel understands it as "everything", I end up with an unwanted result. I also tried variants of it: "",''.

• Text to columns: but since the number of asterisks is not constant, it could not work. Besides, I would have to manually do it for an impressive number of columns so this solution is not practicable.

• Manually removing the stars is out of the question, too.

As an example, here is what I want to do:

A1=1***

A2=2*

A3=3

A4=sum(A1:A3)

Answer 1

The escape character in Excel is the ~. So, to find and replace all asterisks, search for ~* and replace with nothing. Please see the image in order to remove all * characters.