Collecting and adding all Numbers in a cell

MikeIsHere

Dear all,

I need to collect and add all numbers in a cell - the problem is there are also letters and signs in that cell and I do not know how many numbers, letters and signs the cell has (all I know is the numbers are between 1 and 9), e.g.:

"Mo1/1" -> result shall be 2

"1A-3(team) & 4" -> result shall be 8

"1 (max) + 5 team) + 8GL(?)" -> result shall be 14

I thought about checking every digit via the functions len(), left() and Isnumber() but then I would end up with a very long IF-formula and the amount of digits in my cells that are beeing checked would be limited to the length of my IF-formula.

Is there a better way?

Thanks in advance! :)

Kelly Moore

Hey @MikeIsHere

Here's the start of your formula. I cheated on this one and extracted 2 characters past every comma. Is it possible for you to add a helper column for the decimal portion? This would make it easier.

Perhaps for the future you could begin to redesign your sheet to better collect the data you want to evaluate.

Again, I suggest you drop this into Word, etc where you can globally replace the column name with yours.

=VALUE(LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "1", "")) + 2 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "2", ""))) + 3 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "3", ""))) + 4 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "4", ""))) + 5 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "5", ""))) + 6 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "6", ""))) + 7 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "7", ""))) + 8 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "8", ""))) + 9 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "9", "")))) + SUBSTITUTE([Text Column]@row, [Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3))

Kelly

Kelly Moore

Hey @MikeIsHere

Clunky, but this should works. If you drop this first into Word, etc, you can do a global replace of the formula column name before inserting into your sheet. If your column is a multiselect column, we will need to tweak the formula.

=LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "1", ""))+ 2 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "2", ""))) + 3 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "3", ""))) + 4 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "4", ""))) + 5 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "5", ""))) + 6 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "6", ""))) + 7 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "7", ""))) + 8 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "8", ""))) + 9 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "9", "")))

Will this work for you?

Kelly

MikeIsHere

Hey @Kelly Moore ,

thanks for your prompt reply, your formula works perfectly fine :)

Unfortunately I was not specific enough! the numbers I need to collect and add are between 1 and 9 but not necessarily Integer - they have max. two digits after the decimal point. Therefore more adequate examples for my problem would be e.g.:

"Mo1/1,25" -> result shall be 2,25

"1A-3(team) & 0,82" -> result shall be 4,82

"1 (max) + 0,01 team) + 8GL(?)" -> result shall be 9,01

Can you help me out?

Kelly Moore

Hey @MikeIsHere

As I work on the above, can you get a screenshot of actual data? Just that one column. I'm wondering if there is only one potential decimal string in each cell, or, if there's the additional wrinkle of more than one decimal. Also, I want to verify if you're only using the comma as a decimal placeholder, and not also as a text comma?

Kelly Moore

Hey @MikeIsHere

Here's the start of your formula. I cheated on this one and extracted 2 characters past every comma. Is it possible for you to add a helper column for the decimal portion? This would make it easier.

Perhaps for the future you could begin to redesign your sheet to better collect the data you want to evaluate.

Again, I suggest you drop this into Word, etc where you can globally replace the column name with yours.

=VALUE(LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "1", "")) + 2 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "2", ""))) + 3 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "3", ""))) + 4 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "4", ""))) + 5 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "5", ""))) + 6 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "6", ""))) + 7 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "7", ""))) + 8 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "8", ""))) + 9 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "9", "")))) + SUBSTITUTE([Text Column]@row, [Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3))

Kelly

MikeIsHere

Hey @Kelly Moore,

thanks, you helped a lot! :)

Kelly Moore

Hey Mike

I see an error that happens if there is no decimal in the cell (If you agree to a helper column we could simplify the formula a lot!)

In the meantime, to remedy the error, we must add the IFERROR to the formula

=IFERROR(VALUE(LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "1","")) + 2 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "2", ""))) + 3 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "3", ""))) + 4 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) -LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "4", ""))) + 5 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) -LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "5", ""))) + 6 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "6", ""))) + 7 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "7", ""))) + 8 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) - LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "8", ""))) + 9 * (LEN(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), "")) -LEN(SUBSTITUTE(SUBSTITUTE([Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3), ""), "9", "")))) + SUBSTITUTE([Text Column]@row, [Text Column]@row, MID([Text Column]@row, FIND(CHAR(44), [Text Column]@row), 3)), LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "1", "")) + 2 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "2", "")))+ 3 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "3", ""))) + 4 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "4", ""))) + 5 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "5", ""))) + 6 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "6", "")))+ 7 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "7", ""))) + 8 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "8", ""))) + 9 * (LEN([Text Column]@row) - LEN(SUBSTITUTE([Text Column]@row, "9", ""))))

Kelly