Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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? Helllpp!

Thanks

share|improve this question
    
What was the replace arguments? Did you set the replace it with the rest of the number instead? –  Arjang May 11 '12 at 1:00

2 Answers 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.

share|improve this answer
    
+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 '12 at 1:09
    
Thank-you so much you saved me alot of unneccesary agitation! –  Natalie May 11 '12 at 1:24

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.