Excel Theatre Blog Rotating Header Image

Remove Characters Before Numbers: Excel Questions

Questions are often asked in the comments for the Contextures Excel Blog, and get lost in the shuffle.

I’ll post some of the questions here, and please add your suggestions for solving the problems.

How Do I Remove Characters?

Here’s a question from Joanne:

  • What formula do I use if I want to remove * in front of figures on a spreadsheet where the number of digits vary?
  • i.e. ***2.00 – ***20000.00

Suggested Solution

I’d use the SUBSTITUTE function. For example if the first number is in cell A2, put this formula in cell B2:

=--SUBSTITUTE(A2,"*","")

The SUBSTITUTE function replaces the asterisks with nothing, and the two minus signs after the equal sign change the number from text to a real number.

Your Solution

How would you solve the problem? If you have a different solution, please add a comment, to help Joanne.

________

2 Comments

  1. If there’s no need to retain the original string, you could use find and replace (shortcut CTRL + H).

    In the “find what” box search for ~* (the tilde forcing Excel to look for the asterisk rather than taking it as a wildcard).

    Leave the “replace with” box blank which – as in your solution – will replace it with nothing before clicking replace all.

  2. Debra says:

    Thanks Jennifer, great tip!