# Sum contents of single cell

#### errtu

##### Board Regular
I have data sent to me in this format and it is all in a single cell:

Say A1 has

23.1 45.2 35.4 78 12

So, I need to sum all of this numbers. I tried using a macro that replaces the spaces with + sign and then using

Code:
``````Range("A2").Select
ActiveCell.FormulaR1C1 = "=" & Range("A1")``````

It works great except that the people that enter the data do two things sometimes:

1. They press space two or three times so I end up with "23.4++12.1+++45.1"

2. They press space at the end, so I end up with : "23.1+45.1+89+" or sometimes even at the beginning "+23.1+45.1+89"

so I get run time error 1004

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Oaktree

##### MrExcel MVP
Try:

Code:
``[A2] = "=" & Application.WorksheetFunction.Substitute(Trim([A1]), " ", "+")``

#### Ron Coderre

##### MrExcel MVP
With
A1: "23.1 45.2 35.4 78 12 1000 "
(without the quotes, of course)

Perhaps you could use this User Defined Function to return the sum of the cells contents:

Code:
``````Function SumCellContents(rng As Range)
Dim MyCell As Range
Set MyCell = rng.Cells(1, 1)
SumCellContents = Evaluate( _
"SUM(" & Replace(Expression:=Trim(MyCell.Text), _
Find:=" ", Replace:=",") & ")")
End Function``````

Then use
Code:
``A2: =SumCellContents(A1)``
In the above example, the formula returns: 1193.7

Is that something you can work with?

#### T. Valko

##### Well-known Member
I have data sent to me in this format and it is all in a single cell:

Say A1 has

23.1 45.2 35.4 78 12

So, I need to sum all of this numbers. I tried using a macro that replaces the spaces with + sign and then using

Code:
``````Range("A2").Select
ActiveCell.FormulaR1C1 = "=" & Range("A1")``````

It works great except that the people that enter the data do two things sometimes:

1. They press space two or three times so I end up with "23.4++12.1+++45.1"

2. They press space at the end, so I end up with : "23.1+45.1+89+" or sometimes even at the beginning "+23.1+45.1+89"

so I get run time error 1004
Here's an array formula** that will do what you want.

A2 = 23.1 45.2 35.4 78 12

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

** 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.

#### Scott Huish

##### MrExcel MVP
Use Text to Columns with space as the delimiter. Check Treat consecutive delimiters as one.
then use a standard SUM formula.

#### errtu

##### Board Regular
With
A1: "23.1 45.2 35.4 78 12 1000 "
(without the quotes, of course)

Perhaps you could use this User Defined Function to return the sum of the cells contents:

Code:
``````Function SumCellContents(rng As Range)
Dim MyCell As Range
Set MyCell = rng.Cells(1, 1)
SumCellContents = Evaluate( _
"SUM(" & Replace(Expression:=Trim(MyCell.Text), _
Find:=" ", Replace:=",") & ")")
End Function``````
Then use
Code:
``A2: =SumCellContents(A1)``
In the above example, the formula returns: 1193.7

Is that something you can work with?

Beautiful, thank you

Replies
4
Views
347
Replies
1
Views
252
Replies
8
Views
360
Replies
5
Views
269
Replies
1
Views
539

1,191,216
Messages
5,985,318
Members
439,956
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back