summing 2 to 8 separate numbers in a single cell

[Green Fox]

I have a group of numbers in a single cell, i.e.: 14 6 8 14 6 8. I
would like a formula that provides the sum of those numbers. I thought
I had seen a way to do that a few years ago, but my searches are
coming up empty. Should I be using text functions to isolate numbers,
or would I be better off trying to turn it into an array? It doesn't
seem that it should be that hard, but it's confounding me — maybe it's
lack of sleep...

Andy

 

[Domenic]

Here's one way... Assuming that A2 contains the data, try the
following...

Insert > Name > Define

Name: Array

Refers to:

=EVALUATE("{"&SUBSTITUTE($A$2," ",",")&"}")

Click Ok

Then try...

=SUM(Array)

Hope this helps!

http://www.xl-central.com

 

[Green Fox]

Here's one way...  Assuming that A2 contains the data, try the
following...

Insert > Name > Define

Name:  Array

Refers to:

=EVALUATE("{"&SUBSTITUTE($A$2," ",",")&"}")

Click Ok

Then try...

=SUM(Array)

Hope this helps!

http://www.xl-central.com






- Show quoted text -

Thanks Domenic,
That was exactly what I wanted — and so simple!

Andy