google sheets
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?
Google Spreadsheet actually has regex methods. There are 3 methods:
REGEXEXTRACT(text, regular_expression) - Extracts matching substrings according to a regular expression. Example: REGEXEXTRACT("Needle in a haystack", ".e{2}dle")
REGEXMATCH(text, regular_expression) - Whether a piece of text matches a regular expression. Example: REGEXMATCH("Spreadsheets", "S.r")
REGEXREPLACE(text, regular_expression, replacement) - Replaces part of a text string with a different text string using regular expressions. Example: REGEXREPLACE("Spreadsheets", "S.*d", "Bed")
However, because I don't really know how to use regex, I'll show you how I would do it using SPLIT. This will work even if the numbers aren't separated by a specific character (i.e. space) as shown in the example:
=SUM(SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789"))))
Seems to do the job.
Key for the sort is the notation of the digits in the text (String #). These are sorted alphanumerically, meaning that 9 is higher than 25 when sorting descendingly. This can be resolved by squeezing in a zero to all digits ranging from 1 to 9. See formula I constructed.
=SORT( // range
UNIQUE( // range
ARRAYFORMULA( // array_formula
IF(
MID( // logical_expression
A2:A, // string
9, // starting_at
1 // extract_length
)="/",
REPLACE( // value_if_true
A2:A, // text
8, // position
0, // length
"0" // new_text
),
A2:A // value_if_false
)
)
),
1, // sort_column
TRUE // is_ascending
)
copy / paste
=SORT(UNIQUE(ARRAYFORMULA(IF(MID(A2:A, 9, 1)="/",REPLACE(A2:A, 8, 0, "0"), A2:A))), 1, TRUE)
The MID formula separates the 9th character from the string placed in A2:A. If it matches a /, then use the REPLACE formula to insert a 0 at position 8, by using zero as start position. If no match has been found, simply show the unaltered range A2:A. All is wrapped inside an ARRAYFORMULA to take on ranges instead off one cell. The altered range is then fed to the UNIQUE formula that will show only unique entries. This range is sorted by the SORT formula, using the first column of the range (and the only one) and sorted ascendingly.
Best Answer
If you have a row of data like A1:F1 you could use this formula in excel
=SUMPRODUCT((0&MID(A1:F1,{2;7;12;17;22},3))+0)or this in google-spreadsheets
=arrayformula(SUM((0&MID(A1:F1,{2;7;12;17;22},3))+0))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
=SUMPRODUCT((0&MID(A1:A10,{2,7,12,17,22},3))+0)or this in google-spreadsheets
=arrayformula(SUM((0&MID(A1:A10,{2,7,12,17,22},3))+0))blank cells are allowed in the data so you could make that a larger range for expansion purposes.
Explanation:
If A1 contains this string X5.1 Y4.3 Z2.8 then
=MID(A1,{2,7,12,17,22},3)will give you this "array"
{"5.1","4.3","2.8","",""}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
=0&MID(A1,{2,7,12,17,22},3)gives the result
={"05.1","04.3","02.8","0","0"}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:
{5.1,4.3,2.8,0,0}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.......