Sum contents of single cell

[errtu]

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

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

 

[Oaktree]

Try:

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

 

[Ron Coderre]

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:

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

A2: =SumCellContents(A1)

In the above example, the formula returns: 1193.7

Is that something you can work with?

 

[T. Valko]

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

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]

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

 

[errtu]

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:

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

A2: =SumCellContents(A1)

In the above example, the formula returns: 1193.7

Is that something you can work with?

Beautiful, thank you