3

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?

1
  • What was the replace arguments? Did you set the replace it with the rest of the number instead?
    – jimjim
    May 11, 2012 at 1:00

2 Answers 2

2

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.

2
  • +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/…
    – iglvzx
    May 11, 2012 at 1:09
  • Thank-you so much you saved me alot of unneccesary agitation!
    – Natalie
    May 11, 2012 at 1:24
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .