My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

One way...

The formula *must* be entered in the cell to the immediate right of the cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

Another option that isn't so rigid in its implementation...

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/300...-10423159.html

The use this formula (entered anywhere!):

=EVAL(SUBSTITUTE(E1,",","+"))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

The formula *must* be entered in the cell to the immediate right of the
cell that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In this
example what would the formula look like? The amount of numbers in C will
vary in each row.

a b c
d
1 700-2, 88-00,
900-4, 33-00,

2


3

Thanks so much.



"T. Valko" wrote:

One way...

The formula *must* be entered in the cell to the immediate right of the cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

700-2, 88-00,
900-4, 33-00,

Is there a formula that will add these numbers?
87563, 8930, 98279

The "numbers" you just posted don't look anything like the the numbers you
posted in your original sample!

What are the dashes for?

If these are the numbers:

700-2, 88-00,
900-4, 33-00,

Then what results do you expect?

What about that last comma? If there is not another number why is it there?

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In
this
example what would the formula look like? The amount of numbers in C will
vary in each row.

a b c
d
1 700-2, 88-00,
900-4, 33-00,

2


3

Thanks so much.



"T. Valko" wrote:

One way...

The formula *must* be entered in the cell to the immediate right of the
cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in
another
colum, the total of numbers not the sum, e.g. 3

Thanks,

My appologies for not doing a good job of explaining; these numbers are
product #'s, and vary in format, some with dashes some without, they also
vary in the amount of numerals and some have letters. I am interested only
in the total of numbers, as in 4 for the example below. They will always
have a coma and space between the numbers.

Thanks for your quick response.


700-2, 88-00,
900-4, 33-00,

Is there a formula that will add these numbers?
87563, 8930, 98279

The "numbers" you just posted don't look anything like the the numbers you
posted in your original sample!

What are the dashes for?

If these are the numbers:

700-2, 88-00,
900-4, 33-00,

Then what results do you expect?

What about that last comma? If there is not another number why is it there?

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In
this
example what would the formula look like? The amount of numbers in C will
vary in each row.

a b c
d
1 700-2, 88-00,
900-4, 33-00,

2


3

Thanks so much.



"T. Valko" wrote:

One way...

The formula *must* be entered in the cell to the immediate right of the
cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in
another
colum, the total of numbers not the sum, e.g. 3

Thanks,

Count the commas.

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

pandd15 wrote:
My appologies for not doing a good job of explaining; these numbers are
product #'s, and vary in format, some with dashes some without, they also
vary in the amount of numerals and some have letters. I am interested only
in the total of numbers, as in 4 for the example below. They will always
have a coma and space between the numbers.

Thanks for your quick response.


700-2, 88-00,
900-4, 33-00,
Is there a formula that will add these numbers?
87563, 8930, 98279
The "numbers" you just posted don't look anything like the the numbers you
posted in your original sample!

What are the dashes for?

If these are the numbers:

700-2, 88-00,
900-4, 33-00,
Then what results do you expect?

What about that last comma? If there is not another number why is it there?

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
I tried this one =EVALUATE(SUBSTITUTE(A1,",","+")) , I must be doing
something wrong. I am new to Excel so I must be missing something. In
this
example what would the formula look like? The amount of numbers in C will
vary in each row.

a b c
d
1 700-2, 88-00,
900-4, 33-00,

2


3

Thanks so much.



"T. Valko" wrote:

One way...

The formula *must* be entered in the cell to the immediate right of the
cell
that you want to sum. For example:

E1 = 87563, 8930, 98279

The formula *must* be entered in cell F1.

Create this named formula.
Select cell B1. ***this is important***
Goto the menu InsertNameDefine
Name: ESum (or whatever name you want to use)
Refers to:

=EVALUATE(SUBSTITUTE(A1,",","+"))

OK out

Then:

E1 = 87563, 8930, 98279

Enter this formula in **F1** :

=ESum

--
Biff
Microsoft Excel MVP


"pandd15" wrote in message
...
My spreadsheet contains multiple numbers in one cell they are all
seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in
another
colum, the total of numbers not the sum, e.g. 3

Thanks,

pandd15 wrote:
My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,


=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),
(99*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1))+1,99)))

Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))

"pandd15" wrote:

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely
as an example.

Thanks,


"Lori Miller" wrote:

Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))

"pandd15" wrote:

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

To count numbers contained in A1 (with comma or any other delimiter) try:

=SUM(--(FREQUENCY(COLUMN(A:IV),COLUMN(A:IV)*ISERR(-MID("."&A1&".",COLUMN(A:IV),1)))1))

"pandd15" wrote:

Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely
as an example.

Thanks,


"Lori Miller" wrote:

Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))

"pandd15" wrote:

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

Should have read more carefully, if it's only comma separated, perhaps

=LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,",","
"))," ",""))+1


"pandd15" wrote:

Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely
as an example.

Thanks,


"Lori Miller" wrote:

Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))

"pandd15" wrote:

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

Thanks Lori and everyone else for all the help. I have another question
about this same spreadsheet.

I have dates in several columns that track how long a processes take. These
dates are manually added however the process may still be incomplete thus
leaving a blank in that column. These blank columns are causing my
calculations to have incorrect #'s. Is there a way I can leave a cell blank
without having this happen?

"Lori Miller" wrote:

Should have read more carefully, if it's only comma separated, perhaps

=LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,",","
"))," ",""))+1


"pandd15" wrote:

Thanks Lori but by add I mean the total of numbers not the sum, e.g. 87346-1,
838473, 93759-1 = 3 there are usually more than 3 in a cell, I use 3 merely
as an example.

Thanks,


"Lori Miller" wrote:

Maybe this to sum all numbers in A1 (with any non-numeric delimiter):

=SUMPRODUCT(TEXT(MID(A1&".",257-COLUMN(A:IV),1),"0;;0;\0")*10^(COLUMN(A:IV)-
LOOKUP(COLUMN(A:IV),COLUMN(A:IV)/ISERR(-MID(A1&".",257-COLUMN(A:IV),1)))-1))

"pandd15" wrote:

My spreadsheet contains multiple numbers in one cell they are all seperated
by a coma. Is there a formula that will add these numbers?

87563, 8930, 98279 are in one cell I'd like to have the total in another
colum, the total of numbers not the sum, e.g. 3

Thanks,

pandd15 wrote:
Thanks Lori and everyone else for all the help. I have another question
about this same spreadsheet.

I have dates in several columns that track how long a processes take. These
dates are manually added however the process may still be incomplete thus
leaving a blank in that column. These blank columns are causing my
calculations to have incorrect #'s. Is there a way I can leave a cell blank
without having this happen?



Probably, but without more information about your data layout and existing
formulas, a specific answer is not likely.