Formula to sum values extracted from string

Hi all

I'll preface this by pointing out that I do NOT want to use text to
columns, nor a VBA solution. I'm looking for a formula alternative -
maybe an array formula.

I have a single column of data extracted from a string, which
represents numeric values, single space-separated. The data looks like
this:

8.56 2,514.12 3.18 0.35

What I'd ideally like is a formula that sums each of the four values,
which in the above example would be 2,526.21.

Any suggestions appreciated.

8.56 2,514.12 3.18 0.35

Assume that string is in cell A1.

Create this named formula
Goto the menu InsertNameDefine
Name: SumString
Refers to:

=EVALUATE(SUBSTITUTE(SUBSTITUTE(
INDIRECT("RC[-1]",0),",","")," ","+"))

OK

Then, enter this formula in **B1** :

=SumString

The SumString formula **must** be entered in the cell to the immediate right
of the cell being evaluated.

--
Biff
Microsoft Excel MVP


"Paul Martin" wrote in message
...
Hi all

I'll preface this by pointing out that I do NOT want to use text to
columns, nor a VBA solution. I'm looking for a formula alternative -
maybe an array formula.

I have a single column of data extracted from a string, which
represents numeric values, single space-separated. The data looks like
this:

8.56 2,514.12 3.18 0.35

What I'd ideally like is a formula that sums each of the four values,
which in the above example would be 2,526.21.

Any suggestions appreciated.

Many thanks, Biff, especially for responding so promptly. I've
modified the formula slightly for A1 (relative) nomenclature.
Question: how come EVALUATE (not in Excel help at all) is available as
to names but not to a cell formula?

Regards

Paul Martin
Melbourne, Australia

EVALUATE is a macro function. Macro functions were used in very early
versions of Excel and are the predecessors to the current use of VBA in
newer versions of Excel. Macro functions are still supported but newer
versions of Excel don't include any documentation about them.

I'm not sure of the technical reason for it but, as you noticed, these macro
functions can't be used directly as worksheet formulas. You have to use the
intermediate step of creating a defined named formula first. One of the big
drawbacks to using these macro functions is that since you first have to
create a defined named formula, trying to use a relative cell reference is
kind of tricky (read: very complicated!).

We had a discussion about this very issue just last week. See this:

http://groups.google.com/group/micro...6e0e8996e2c097

Microsoft has the macro function help files available for download at:

http://support.microsoft.com/kb/128185

--
Biff
Microsoft Excel MVP


"Paul Martin" wrote in message
...
Many thanks, Biff, especially for responding so promptly. I've
modified the formula slightly for A1 (relative) nomenclature.
Question: how come EVALUATE (not in Excel help at all) is available as
to names but not to a cell formula?

Regards

Paul Martin
Melbourne, Australia

The defined name formula can be shorter:
=EVALUATE(SUBSTITUTE(TEXT(INDIRECT("RC[-1]",0),"#")," ","+"))
Micky


"Paul Martin" wrote:

Many thanks, Biff, especially for responding so promptly. I've
modified the formula slightly for A1 (relative) nomenclature.
Question: how come EVALUATE (not in Excel help at all) is available as
to names but not to a cell formula?

Regards

Paul Martin
Melbourne, Australia
.

You could also try this array formula. With text in A1, Ctrl+Shift+Enter:

=SUM(IF(MID(" "&A1,COLUMN(1:1),1)=" ",--(0&MID(A1,COLUMN(1:1),FIND(" ",A1&"
",COLUMN(1:1))-COLUMN(1:1)))))


"Paul Martin" wrote in message
...
Hi all

I'll preface this by pointing out that I do NOT want to use text to
columns, nor a VBA solution. I'm looking for a formula alternative -
maybe an array formula.

I have a single column of data extracted from a string, which
represents numeric values, single space-separated. The data looks like
this:

8.56 2,514.12 3.18 0.35

What I'd ideally like is a formula that sums each of the four values,
which in the above example would be 2,526.21.

Any suggestions appreciated.

Thanks for that info, Biff.

Micky, your suggestion doesn't work for me. The TEXT function still
doesn't seem to work with the commas and I get an error when I number
is over 999.

Lori, that looks great, though it fails where there's a negative
number. I'm reasonably familiar with array formulas, but I can't get
my head around this one. Could you suggest a fix for when there's a
negative number and could you also describe how your array formula is
working?

Thanks all of you

Paul


On Jan 21, 1:38*am, "Lori Miller"
wrote:
You could also try this array formula. With text in A1, Ctrl+Shift+Enter:

=SUM(IF(MID(" "&A1,COLUMN(1:1),1)=" ",--(0&MID(A1,COLUMN(1:1),FIND(" ",A1&"
",COLUMN(1:1))-COLUMN(1:1)))))

"Paul Martin" wrote in message

...

Hi all

I'll preface this by pointing out that I do NOT want to use text to
columns, nor a VBA solution. *I'm looking for a formula alternative -
maybe an array formula.

I have a single column of data extracted from a string, which
represents numeric values, single space-separated. The data looks like
this:

8.56 2,514.12 3.18 0.35

What I'd ideally like is a formula that sums each of the four values,
which in the above example would be 2,526.21.

Any suggestions appreciated.

Also, pardon my ignorance, but what does "--" in a formula mean?

Paul


On Jan 21, 9:57*am, Paul Martin wrote:
Thanks for that info, Biff.

Micky, your suggestion doesn't work for me. *The TEXT function still
doesn't seem to work with the commas and I get an error when I number
is over 999.

Lori, that looks great, though it fails where there's a negative
number. *I'm reasonably familiar with array formulas, but I can't get
my head around this one. *Could you suggest a fix for when there's a
negative number and could you also describe how your array formula is
working?

Thanks all of you

Paul

On Jan 21, 1:38*am, "Lori Miller"

wrote:
You could also try this array formula. With text in A1, Ctrl+Shift+Enter:

=SUM(IF(MID(" "&A1,COLUMN(1:1),1)=" ",--(0&MID(A1,COLUMN(1:1),FIND(" ",A1&"
",COLUMN(1:1))-COLUMN(1:1)))))

"Paul Martin" wrote in message

....

Hi all

I'll preface this by pointing out that I do NOT want to use text to
columns, nor a VBA solution. *I'm looking for a formula alternative -
maybe an array formula.

I have a single column of data extracted from a string, which
represents numeric values, single space-separated. The data looks like
this:

8.56 2,514.12 3.18 0.35

What I'd ideally like is a formula that sums each of the four values,
which in the above example would be 2,526.21.

Any suggestions appreciated.

Paul - try removing "0&" from the formula to allow for negative values.
This was inserted in case there were any extra spaces, but you could
also use TRIM(A1) instead of A1 to deal with this.

Let's consider the example A1="1 4.1 -5"
To see how it works, highlight parts of the formula and press F9 or use
the Evaluate Formula tool.

COLUMN(1:1) generates an array of numbers which for this example
we can take to be {1,2,3,4,5,6,7,8,9}.

The first part of the formula returns each character in an array with
a leading space:
MID(" "&A1,{1,2,3,4,5,6,7,8,9},1)={" ","1"," ","4",".","1"," ","-","5"}

If there is a space, the second part of the IF formula is calculated.
This part returns the number between each space by finding the
location of the next space from the current position (an extra
space is added to the end to find the length of the last number.)

"--" is a way of converting a text value to a numeric value, it's like a
shorthand form of the VALUE() function. Other ways to do this are
"1*" or "0+". This all means the result of the IF statement is:

SUM({1,FALSE,4.1,FALSE,FALSE,FALSE,-5,FALSE,FALSE})

and since SUM() ignores text and logical values, the return value is 0.1.

Thanks Lori

Paul


On Jan 21, 10:47*pm, "Lori Miller"
wrote:
Paul - try removing "0&" from the formula to allow for negative values.
This was inserted in case there were any extra spaces, but you could
also use TRIM(A1) instead of A1 to deal with this.

Let's consider the example A1="1 4.1 -5"
To see how it works, highlight parts of the formula and press F9 or use
the Evaluate Formula tool.

COLUMN(1:1) generates an array of numbers which for this example
we can take to be {1,2,3,4,5,6,7,8,9}.

The first part of the formula returns each character in an array with
a leading space:
MID(" "&A1,{1,2,3,4,5,6,7,8,9},1)={" ","1"," ","4",".","1"," ","-","5"}

If there is a space, the second part of the IF formula is calculated.
This part returns the number between each space by finding the
location of the next space from the current position (an extra
space is added to the end to find the length of the last number.)

"--" is a way of converting a text value to a numeric value, it's like a
shorthand form of the VALUE() function. Other ways to do this are
"1*" or "0+". This all means the result of the IF statement is:

SUM({1,FALSE,4.1,FALSE,FALSE,FALSE,-5,FALSE,FALSE})

and since SUM() ignores text and logical values, the return value is 0.1.