Getting Google Spreadsheets to interpret string as formula

March 12, 2009 10:53 AM

I have hundreds of text strings entered through a form in a Google Spreadsheets which look like 4*3 9*2 17*3 22 37*4 52. I can easily substitute " " for "+" and concatenate a "=" in front =4*3+9*2+17*3+22+37*4+52. Now I have a text string which looks like a formula but isn't one. How can I make Google Spreadsheets interpret this string as a formula?

These Excel solution don't apply because I'm not always around when someone makes a new form entry.

With =sum(split(A1,"+")) it's possible to interpret a string like 4+9+17+22 and get a correct result.

However =multiply(split(A1,"*")) does not work for 17*3 ("Wrong number of arguments", even though split should create 2 arguments here). So a combination of splits, multiply and sum also fail.

Is there another solution?

posted by Akeem to Computers & Internet (3 answers total)

Best answer: =product(split(a1,"*")) does work
posted by adamrice at 11:21 AM on March 12, 2009

Copy all the cells and paste them into excel. They get properly calculated. then copy and paste them back into google docs.
posted by nyc_consultant at 5:16 PM on March 12, 2009

Response by poster: I was looking for a solution where I do not have to intervene to get a result. A lot of people can make an entry through the form and they like to see the results (displayed in a graph) immediatly. They can not wait until somebody drops by.

As the product funtion works, I can now look for the correct order of splits, sum and multiply to do these calculations in 1 step.
posted by Akeem at 1:35 AM on March 13, 2009