Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. Since the doc holds a few 10s of this format - any suggestion on how to convert these would be highly appreciated (like if it can be done in MS Excel that's OK as well).

What I was thinking was a awk script sort of thing: find all round(*) replace with round(*,0)... of course this will have to ignore all the round(*,2) that I also have.

Tried to use the find and replace menu but it doesn't find round at all (and believe me it is there).

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You can find them using find and replace if you go to view -> all formulas (ctrl+') and then do you "round( " find this will at least cut down on the manual labor of it all

I couldnt find a way (using find and replace to search for wild card value though

EDIT:

Try out the script below REF: http://www.google.com/support/forum/p/Google%20Docs/thread?tid=288d77c8c9ad7696&hl=en

function SearchReplaceInFormula() {

  var pattern = Browser.inputBox("Enter the search string:");
  var replacement = Browser.inputBox("Enter the replacement string:");
  if (pattern == "" && replacement == "")
  {
    Browser.msgBox("Nothing to search and or replace");
    return;
  }

  var formula = SpreadsheetApp.getActiveRange().getFormulaR1C1();
  var range = SpreadsheetApp.getActiveRange();
  var crows = range.getNumRows();
  var ccols = range.getNumColumns();

  var row = 0;
  for (row = 1; row <= crows; row++)
  {
       for (col=1; col <= ccols; col++)
       {
         var cell = range.getCell(row, col);
         var formula = cell.getFormula();
         var updated = formula;
         while(-1 != updated.indexOf(pattern))
         {
           updated = updated.replace(pattern, replacement);
         }
         if (formula != updated)
         {
           cell.setFormula(updated)
         }
       }
  }

  Browser.msgBox("Done replacing " + pattern + " with " + replacement);
}
share|improve this answer
    
thanks for the solution (not what I expected and not perfect - but gets the job done!). Would have given you two up votes for the tip in the beginning (view all formulas) but can only up vote once :-) –  Dror Cohen Jun 1 '11 at 17:56
add comment

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.