Function to count numbers in one Cell IE 1+2+10

M

Mitch

I'm having difficulty finding a function to count a series of numbers in a
single cell.

Heres an example of the data, 1+10+4+5+15+1

So the total should be 36 but since the user writes this info in one cell,
and 'sum of that cell' doesn't seem to work, is there something function
wise, simple that I'm missing?

Thanks
 
M

Mitch

Except we need to keep that one field with all the ones. The only other thing
I can think of is to copy the contents over to the neighbouring field, then
have it sum it.

Just wish I knew a quick way of doing it automatically.
 
G

Gord Dibben

Mitch

Ron mentioned using a User Defined Function.


Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function


With 1+10+4+5+15+1 in A1 enter =EvalCell(A1) in B1

With your workbook open hit Alt + F11 to open the VBE.

CTRL + r to open Project Explorer.

Select your workbook/project and Right-click>Insert>Module.

Copy/paste the above Function into that module.


Gord Dibben MS Excel MVP
 
Top