I am trying to get the sum of a column of cells, which sometimes contains numbers and sometimes contains a string with numbers in it. I've found that the formula
works for parsing strings into numbers and SUMming them, but because of regex, it ignores anything that is not a string (ie, already a number). An example set of data might be:
B2: 1000.56 B3: $254.32 B4: ~125 B5: 321.43 B6: ~1030
The result of which should be
1000.56 + 254.32 + 125 + 321.43 + 1030 = 2731.31
Using the above formula though, the values of B2 and B5 are ignored, as they are not a string, and so regex will not parse them
Is this possible, or would it end up just being completely convoluted?