Formula for adding mutiple numbers in one cell

[joeybisho]

Hi, I have mutiple numbers in one cell that I would like to be added together into the column beside it.
eg H32
24.00 12.00 24.00 12.00 12.00

they all seem to be seperated by either 6 or 7 spaces.

Can someone please help me out with a formula for this?

Thanks

[FDibbins]

Hi and welcome to the forum

I would suggest that you use text-to-columns to 1st break out the numbers into seperate columns, and then add up the row

[joeybisho]

Thank you, I had already thought of that, but this will be a weekly task and I was trying to simplify it as there will already be a lot of data entry in extra columns. So thats why I wanted a formula, is there any way to create a formula that adds multiple numbers in one cell separated by multiple spaces?

[FDibbins]

will the format ALWAYS be....
24.00 12.00 24.00 12.00 12.00?

ie 2 numbers, period, 2 numbers, space...repeat

[Teethless mama]

See the attached file

[joeybisho]

Pretty much, example of a few lines would be
18.00 9.00
1.00 1.00
4.00 1.00
12.00 24.00 12.00
24.00 12.00 24.00 12.00 12.00
But it seems the amount of spaces varies on the amount of numbers in that line. eg cells with 3 or 4 numbers have 6 spaces and a cell with 2 numbers has 7 spaces if that makes any difference?

[joeybisho]

Yep that is awesome, thank you very much!

[Teethless mama]

You're Welcome!

[Tony Valko]

Assuming your data is in the range A2:A6.

Enter this array formula** in B2 and copy down as needed:

=SUM(IF(MID(" "&A2,COLUMN(A2:CV2),1)=" ",--(0&MID(A2,COLUMN(A2:CV2),FIND(" ",A2&" ",COLUMN(A2:CV2))-COLUMN(A2:CV2)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assuming the total numbers of characters in a cell will be <=100.