Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a series of cells in a Google Spreadsheet containing string such as:

A1 = "X5.1 Y4.3 Z2.8"
B1 = "X2.5 Y1.9 Z4.8"
C1 = "X6.2 Y1.9 Z3.4"

I need to get the sum of all the numbers that appear inside the strings. In other words, to have a formula which will effectively sum 5.1+4.3+2.8+2.5+1.9+4.8+6.2+1.9+3.4 .

The number of cells to be summed in such a way is dynamic, and typically much more than 3.

Is there way to achieve this elegantly?

share|improve this question

migrated from superuser.com Feb 6 '13 at 15:01

This question came from our site for computer enthusiasts and power users.

you show a very consistent format, i.e. 3 single letters all followed by a decimal number with a single decimal place and a single digit before the decimal. In your real data is that the same - how large or small can the numbers be - will there always be 3 numbers per cell, will there be single letters with spaces between as shown? – barry houdini Feb 6 '13 at 13:13
in my real data there is always a single letter followed by a number which will have exactly one digit in front of the decimal point, but potentially, if it's a round number, appear just as "X3". however, i'd be happy to accept a solution which requires "X3.0" and i'll adjust the data. there may be more than 3 letter+number pairs per cell. – Luke Feb 6 '13 at 14:07
Does the data all show up in Row 1 -- or, can you transpose it into Column A? – F106dart Feb 6 '13 at 14:19
up vote 5 down vote accepted

If you have a row of data like A1:F1 you could use this formula in excel


or this in google-spreadsheets


That assumes each number is 3 characters like 3.5 and allows for up to 5 numbers per cell - you can extend the {2;7;12;17;22} part if there might be more than 5 per cell - or for high numbers that part can be automated based on cell length.

If you have a column of data like A1:A10 then the separators in {2;7;12;17;22} need to change to commas so that would be like this in excel


or this in google-spreadsheets


blank cells are allowed in the data so you could make that a larger range for expansion purposes.


If A1 contains this string X5.1 Y4.3 Z2.8 then


will give you this "array"


Notice that the values are included in quotes which means they are text values (MID function like LEFT and RIGHT etc. always returns text values) so we need to convert these text strings to numbers before they can be summed - one way to convert is to do a mathematical operation on that array that won't change the values, e.g. *1 or +0. If we use the latter, though, we get this:

{"5.1","4.3","2.8","",""}+0 = {5.1,4.3,2.8,#VALUE!,#VALUE!}

applying +0 to the non-numeric blank [""] values gives #VALUE! error....which is a problem if we want to sum the results.......so, before adding zero we can concatenate a zero to the front of each result, e.g. using


gives the result


concatenating a zero to the front of each number won't chage the value of the numeric values but converts the blanks to zeroes, so now when zero is added we get no errors, just:


which can be summed without error.

Extending the range to A1:A10 simply means that the resultant array is 10x5 rather than 1x5 - everything else works the same way.......

share|improve this answer
fantastic! many thanks! – Luke Feb 6 '13 at 18:35
can you explain the function of the "0&" and "+0" parts? – Luke Feb 8 '13 at 16:47
I added an explanation to my answer..... – barry houdini Feb 8 '13 at 18:52
FYI in google spreadsheets removing the "0&" yields the same results -- perhaps it is only required in excel. – Luke Feb 9 '13 at 14:39

Your Answer


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.