I'm using Excel 2010 and have a number of cells that contain *10^9 e.g. 5*10^9, 3.4*10^9 and need to remove the *10^9. Given that there is about 50000 entries I don't particularly want to do it manually but I'm not very saavy with excel and am not sure how to do this. I tried using the find and replace function however when I did this it removed the entire cell content where as I want to keep the number preceeding the *10^9. I'm sure there must be a way to do this but I don't know how! Any ideas or tips?
2 Answers
When you use an asterisk *
in an Excel find, it means "match any sequence of characters". So
*10^9
means match the entire cell up to *10^9. So when you replace, it replaces everything.
To mean a literal *
you need to escape it with an tilde:
~*10^9
This means match the part of the cell starting containing only *10^9
. You can then leave the replace field blank and it will remove this from the cells.
-
+1 We posted the same answer at the same time, but I like your explanation a lot better [deleted!]. Here's some more info from Microsoft on Excel's find&replace: office.microsoft.com/en-us/excel-help/…– iglvzxMay 11, 2012 at 1:09
-
The problem is the * character, which is treated as a wildcard. To stop this from happening, precede it by the ~ character.
In other words, search for "~*10^9" and replace with nul