Good morning!
I have this basic problem on Numbers: I would like a single cell of text and numbers to have a final sum.
For example:
CELL
10, 4 (Bell), 6, 23 (Josh)
SUM
43€
I would like to reach something like this under, but I find it solved on the web only on Excel. How about Numbers?
Thank you!
MacBook Pro 13″, macOS 11.5
Here is a way that will do up to five different sets of numbers that can contain the digits 0-9, comma, and decimal point.
=IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",1),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",2),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",3),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",4),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",5),0)
If you will have more than 5 numbers, copy one of the pieces of the formula, add it to the end, and increase the number in REGEX.EXTRACT by 1. In other words, follow the same pattern as in the formula above.
REGEX.EXTRACT has the ability to extract an array of all of the numbers but they will be in text form and no SUM function will sum text values so the array cannot be summed. So it was necessary to use the + operator. If someone knows a way to sum the array directly, that would be great.
Here is a way that will do up to five different sets of numbers that can contain the digits 0-9, comma, and decimal point.
=IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",1),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",2),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",3),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",4),0)+IFERROR(REGEX.EXTRACT(A2,"[\d,.]+",5),0)
If you will have more than 5 numbers, copy one of the pieces of the formula, add it to the end, and increase the number in REGEX.EXTRACT by 1. In other words, follow the same pattern as in the formula above.
REGEX.EXTRACT has the ability to extract an array of all of the numbers but they will be in text form and no SUM function will sum text values so the array cannot be summed. So it was necessary to use the + operator. If someone knows a way to sum the array directly, that would be great.
If you use a comma fora decimal separator, the parameters in functions need to be separated by semicolons, not commas. That could be a syntax error
I included commas in the list of acceptable characters to extract but you have commas elsewhere in the text (not as thousands separators or decimal separators) so that is going to cause problems. It will cause an "expects a number" error.
Hopefully all your numbers are integers (no decimals, no commas, only digits 0-9). Below I redid the first part of the formula. You can copy/paste it a few times with a + between each then change the numbers like I talked about before.
=IFERROR(REGEX.EXTRACT(A2;"\d+";1);0)
If you do have decimal numbers, I'll post the regular expression for doing them.
First of all, thank you so much!
I am trying to insert this - I am Italian and just changed the language of the inputs, anyway this "syntax error" that was happening at the beginning still occurs.
What is wrong? I tried with more but if still one isn't working, what could I do?
Thank you so much!
Thank you so so much! It works perfectly! You saved my day!
(:
Sum just numbers in Numbers Text single cell
