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

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

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

On Thu, 30 Aug 2007 11:22:02 -0700, Mitch
wrote:

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

You could select the cell, then put an = or + sign in front of the 1 (on the
formula bar).

Or you could use a VBA function or macro to evaluate the contents.
--ron

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

Just add an "=" before your figures
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Mitch" wrote:

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

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

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.

"kassie" wrote:

Just add an "=" before your figures
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Mitch" wrote:

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

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

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.

I'm not familiar with running macros. I'll see what I can do, or if you have
a suggestion. I made a macro, but obviously it only takes from H26, and
pastes static info. Is there a way to select the field your beside
(neighbouring field to the left, copy and add = in front of 'whatever it
grabs'?


Range("H26").Select
Selection.Copy
Range("I26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=" + H26
Range("I27").Select
End Sub

"Ron Rosenfeld" wrote:

On Thu, 30 Aug 2007 11:22:02 -0700, Mitch
wrote:

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

You could select the cell, then put an = or + sign in front of the 1 (on the
formula bar).

Or you could use a VBA function or macro to evaluate the contents.
--ron

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

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

Copy/paste the above Function into that module.


Gord Dibben MS Excel MVP

On Thu, 30 Aug 2007 12:30:04 -0700, Mitch
wrote:

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.

"kassie" wrote:

Just add an "=" before your figures
--
Hth

Kassie Kasselman
Change xxx to hotmail


"Mitch" wrote:

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

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

On Thu, 30 Aug 2007 12:48:02 -0700, Mitch
wrote:

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.

I'm not familiar with running macros. I'll see what I can do, or if you have
a suggestion. I made a macro, but obviously it only takes from H26, and
pastes static info. Is there a way to select the field your beside
(neighbouring field to the left, copy and add = in front of 'whatever it
grabs'?


Range("H26").Select
Selection.Copy
Range("I26").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=" + H26
Range("I27").Select
End Sub

"Ron Rosenfeld" wrote:

On Thu, 30 Aug 2007 11:22:02 -0700, Mitch
wrote:

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

You could select the cell, then put an = or + sign in front of the 1 (on the
formula bar).

Or you could use a VBA function or macro to evaluate the contents.
--ron


Macro would be simply:

==================
Function eval(rg As Range)
eval = Evaluate(rg.Text)
End Function
=====================
--ron