Formula to sum values extracted from string

[Paul Martin]

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.

 

[T. Valko]

8.56 2,514.12 3.18 0.35

Assume that string is in cell A1.

Create this named formula
Goto the menu Insert>Name>Define
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.

 

[Paul Martin]

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

 

[T. Valko]

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/micr...97?hl=enƖe0e8996e2c097&tvc=1#406e0e8996e2c097

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

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Lori Miller]

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]

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

 

[Paul Martin]

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

Paul

 

[Lori Miller]

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.

 

[Paul Martin]

Thanks Lori

Paul

 

[Paul Martin]

I'm currently using Lori's array formula solution to my original
problem and am now looking for a solution to a variation. Using my
above example:

8.56 2,514.12 3.18 0.35

how would I sum each value in this string EXCEPT for the third value,
in this case "3.18"?

TIA

Paul