Remove Characters Before Numbers: Excel Questions
Questions are often asked in the comments for the Contextures Excel Blog, and sometimes those questions get overlooked, and 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 is one of those questions, from a commenter named Joanne:
- What formula do I use if I want to remove asterisks (*) in front of figures in a spreadsheet column, where the number of digits vary?
- For example, this is in cell A2: ***2.00 – ***20000.00
Solution 1 – Formula
To get rid of asterisks in those cells, I’d use the SUBSTITUTE function.
- For example if the first number is in cell A2, put this formula in cell B2:
=--SUBSTITUTE(A2,"*","")
- Then, copy that formula down to the remaining rows
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.
Solution 2 – Formula (Excel 365)
If you have Excel 365, with TEXTJOIN, and the new array functions, there is a formula on my Contextures site that will remove ALL non-numeric characters from a text string.
Go to the Combine Text and Numbers page, and in the TEXTJOIN section, you can see the formula and its explanation – Example 6 – Remove Non-Numeric Characters
Suggested Solution – Find and Replace
Thanks to Jennifer Deacon, who suggested the following Find and Replace solution, if you don’t need to keep the original data, and want to remove the asterisks only:
- First, select all the cells where you want to remove asterisks
- Next, use the keyboard shortcut, Ctrl+H, to open the Find and Replace dialog box, with the Replace tab active
- Then, in the Find What box, type this: ~*
- The tilde (~) tells Excel to look for the asterisk (*)
- Otherwise, the * is used as a wildcard, representing “any characters”
- Leave the Replace With box empty
- Excel will replace each asterisk with nothing
- Finally, click the Replace All button, ro remove the asterisks from the selected cells
Your Solution
How would you solve the problem? If you have a different solution, please add a comment, to help Joanne, or anyone else with a similar problem.
________
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.
Thanks Jennifer, great tip!