Total sum from the Text

[Ennlamp]

Hi,

I have a database, where some numeric info is part of the standardized text (column L).  Can you help me with the totals in a separate column (column M). Some of the number in column L are with commas and some with dots. The total column M should be with dots as decimal.

Thanks,

Enn

[#2 · Accepted solution]

@Ennlamp  What I think you want is a number of functions combined.  I have created 4 Lambda functions in the attached workbook and demonstrated them working:

CleanNumbers - this lambda will see if excel recognizes a value as a number but if not it will swap all the commas and periods and try again and if still not it will return 0

ExtractNumbers - this lambda will take any string and create an output string of just the 'numeric' values separated by ";" so "test12and(56.34)" will output "12;56.34"

Txt2Array - this lambda will take a string and delimitator character and create an array (i.e. the output from Extract Numbers and make it an array) (note I believe this is one of those functions in beta already)

SumNumsFromTxt - this lambda combines all the above into a single function to sum all the numeric values found inside the array of text strings. 

[#3]

Hi @Ennlamp, 

first of all I would like to point out that the solution of @mtarler is perfect and I would recommend to use it.

Only for those who currently the Lambda function is not yet available, I have combined a function that also works with older Excel versions.

Provided that the weights are always between "(" and "kg" they will be summed up line by line. For the total sum you need a SUM().

[#4]

Well done @mtarler & @dscheikey . Thank you! Works like charm.  

[#5]

@dscheikey Thank you and nice work on a version that doesn't use LET or LAMBDA. That said, you need to replace SEQUENCE() (also a new function) with something like ROW(A$1:INDEX(A:A, [insert # here] )). That said you inspired me to create a new version also.  I took the same approach as my LAMBDA version:

=MAX(
SUM(IFERROR(--MID(
    SUBSTITUTE(
            CONCAT(IFERROR(INDEX({0,1,2,3,4,5,6,7,8,9,",","."},SEARCH(MID(L3,ROW(A$1:INDEX(A:A,LEN(L3))),1),"0123456789,.")),";")),
            ";",
            REPT(" ",LEN(L3))),
     (ROW(A$1:INDEX(A:A,LEN(L3)))-1)*LEN(L3)+1,
     LEN(L3)),0)),
SUM(IFERROR(--MID(SUBSTITUTE(CONCAT(IFERROR(INDEX({0,1,2,3,4,5,6,7,8,9,".",","},SEARCH(MID(L3,ROW(A$1:INDEX(A:A,LEN(L3))),1),"0123456789,.")),";")),";",REPT(" ",LEN(L3))),(ROW(A$1:INDEX(A:A,LEN(L3)))-1)*LEN(L3)+1,LEN(L3)),0)))

so working inside out on line 4 is the formula to replace every character that is not a number or "," or "." with ";".  Then line 3-6 replaces all those ";" with long spaces so that the MID  on line 2 will chop it up into an array of items (using start point array on row 7 and length on row 8) between those ";" (results in a lot of blanks elements and a few numbers that we want).  The IFERROR on line 2 works with the "--" before the MID to say if excel can't convert something to an actual number then replace it with a 0 and finally sum up all the found numbers.

Line 9 is a repeat of lines 2-8 except I swap the "," and "."

Line 1 takes the max of the 2 versions assuming 1 will be formatted the way excel is configured and the other won't and result in all 0s.

a couple features of this include it allows number >1,000 (i.e. 1,245.5 has both a "," and ".") and doesn't rely on starting with a "(" or followed by "kg".

weaknesses include if there is a list of numbers like 34,23,15 those won't be recognized but if there is a space after the "," as there should be then it should work. It also will give odd results if formats are mixed like "you might record 3,45kg but I recorded 4.76kg" but again who would do that.  and the biggest weakness is readability and re-usability as compared to the new LAMBDA version.